<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Chapter 20 Using MySQL as a Document Store</title>
<link rel="stylesheet" href="mvl.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets + chunker.py v1.9.2" />
<link rel="start" href="index.html" title="{book-title}" />
<link rel="up" href="" title="" />
<link rel="prev" href="mysql-shell-userguide.html" title="Chapter 19 MySQL Shell" />
<link rel="next" href="mysql-innodb-cluster-userguide.html" title="Chapter 21 InnoDB Cluster" />
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Chapter 20 Using MySQL as a Document Store</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="mysql-shell-userguide.html">Prev</a> </td>
<th width="60%" align="center"></th>
<td width="20%" align="right"> <a accesskey="n" href="mysql-innodb-cluster-userguide.html">Next</a></td>
</tr>
</table>
<hr>
</div>
<div class="chapter">
<div class="titlepage">
<div>
<div>
<h1 class="title"><a name="document-store"></a>Chapter 20 Using MySQL as a Document Store</h1>

</div>

</div>

</div>
<div class="toc">
<p><b>Table of Contents</b></p><dl class="toc"><dt><span class="section"><a href="document-store.html#document-store-interfaces">20.1 Interfaces to a MySQL Document Store</a></span></dt><dt><span class="section"><a href="document-store.html#document-store-concepts">20.2 Document Store Concepts</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript">20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store</a></span></dt><dd><dl><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-download">20.3.1 Import Database Sample</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-shell">20.3.2 MySQL Shell</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-documents-collections">20.3.3 Documents and Collections</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-relational-tables">20.3.4 Relational Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-documents-in-tables">20.3.5 Documents in Tables</a></span></dt></dl></dd><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python">20.4 Python Quick-Start Guide: MySQL Shell for Document Store</a></span></dt><dd><dl><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-download">20.4.1 Import Database Sample</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-shell">20.4.2 MySQL Shell</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-collections">20.4.3 Documents and Collections</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-relational-tables">20.4.4 Relational Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-in-tables">20.4.5 Documents in Tables</a></span></dt></dl></dd><dt><span class="section"><a href="document-store.html#x-plugin">20.5 X Plugin</a></span></dt><dd><dl><dt><span class="section"><a href="document-store.html#x-plugin-checking-installation">20.5.1 Checking X Plugin Installation</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-disabling">20.5.2 Disabling X Plugin</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-ssl-connections">20.5.3 Using Secure Connections with X Plugin</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-sha2-cache-plugin">20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-options-variables">20.5.5 X Plugin Options and Variables</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-system-monitoring">20.5.6 Monitoring X Plugin</a></span></dt></dl></dd></dl>
</div>
<a class="indexterm" name="idm140091613238640"></a><a class="indexterm" name="idm140091613237600"></a><a class="indexterm" name="idm140091613236112"></a><p>
    This chapter introduces an alternative way of working with MySQL as
    a document store, sometimes referred to as <span class="quote">“<span class="quote">using
    NoSQL</span>”</span>. If your intention is to use MySQL in a traditional
    (SQL) way, this chapter is probably not relevant to you.
  </p><p>
    Traditionally, relational databases such as MySQL have usually
    required a schema to be defined before documents can be stored. The
    features described in this section enable you to use MySQL as a
    document store, which is a schema-less, and therefore
    schema-flexible, storage system for documents. For example, when you
    create documents describing products, you do not need to know and
    define all possible attributes of any products before storing and
    operating with the documents. This differs from working with a
    relational database and storing products in a table, when all
    columns of the table must be known and defined before adding any
    products to the database. The features described in this chapter
    enable you to choose how you configure MySQL, using only the
    document store model, or combining the flexibility of the document
    store model with the power of the relational model.
  </p><p>
    To use MySQL as a document store, you use the following server
    features:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        X Plugin enables MySQL Server to communicate with clients
        using X Protocol, which is a prerequisite for using MySQL
        as a document store. X Plugin is enabled by default in
        MySQL Server as of MySQL 8.0. For instructions to
        verify X Plugin installation and to configure and monitor X
        Plugin, see <a class="xref" href="document-store.html#x-plugin" title="20.5 X Plugin">Section 20.5, “X Plugin”</a>.
      </p></li><li class="listitem"><p>
        X Protocol supports both CRUD and SQL operations,
        authentication via SASL, allows streaming (pipelining) of
        commands and is extensible on the protocol and the message
        layer. Clients compatible with X Protocol include
        MySQL Shell and MySQL 8.0 Connectors.
      </p></li><li class="listitem"><p>
        Clients that communicate with a MySQL Server using
        X Protocol can use X DevAPI to develop applications.
        X DevAPI offers a modern programming interface with a
        simple yet powerful design which provides support for
        established industry standard concepts. This chapter explains
        how to get started using either the JavaScript or Python
        implementation of X DevAPI in MySQL Shell as a client.
        See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/" target="_top">X DevAPI User Guide</a> for in-depth tutorials
        on using X DevAPI.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="document-store-interfaces"></a>20.1 Interfaces to a MySQL Document Store</h2>

</div>

</div>

</div>
<p>
      To work with MySQL as a document store, you use dedicated
      components and a choice of clients that support communicating with
      the MySQL server to develop document based applications.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The following MySQL products support X Protocol and
          enable you to use X DevAPI in your chosen language to
          develop applications that communicate with a MySQL Server
          functioning as a document store:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              MySQL Shell (which provides implementations of
              X DevAPI in JavaScript and Python)
            </p></li><li class="listitem"><p>
              Connector/C++
            </p></li><li class="listitem"><p>
              Connector/J
            </p></li><li class="listitem"><p>
              Connector/Node.js
            </p></li><li class="listitem"><p>
              Connector/NET
            </p></li><li class="listitem"><p>
              Connector/Python
</p></li></ul>
</div>
</li><li class="listitem"><p>
          MySQL Shell is an interactive interface to MySQL supporting
          JavaScript, Python, or SQL modes. You can use MySQL Shell to
          prototype applications, execute queries and update data.
          <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html" target="_top">Installing MySQL Shell</a> has instructions to
          download and install MySQL Shell.
        </p></li><li class="listitem"><p>
          The quick-start guides (tutorials) in this chapter help you to
          get started using MySQL Shell with MySQL as a document
          store.
        </p><p>
          The quick-start guide for JavaScript is here:
          <a class="xref" href="document-store.html#mysql-shell-tutorial-javascript" title="20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store">Section 20.3, “JavaScript Quick-Start Guide: MySQL Shell for Document Store”</a>.
        </p><p>
          The quick-start guide for Python is here:
          <a class="xref" href="document-store.html#mysql-shell-tutorial-python" title="20.4 Python Quick-Start Guide: MySQL Shell for Document Store">Section 20.4, “Python Quick-Start Guide: MySQL Shell for Document Store”</a>.
        </p></li><li class="listitem"><p>
          The <em class="citetitle">MySQL Shell User Guide</em> at
          <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/" target="_top">MySQL Shell 8.0 (part of MySQL 8.0)</a> provides detailed information
          about configuring and using MySQL Shell.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="document-store-concepts"></a>20.2 Document Store Concepts</h2>

</div>

</div>

</div>
<p>
      This section explains the concepts introduced as part of using
      MySQL as a document store.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h3 class="title"><a name="idm140091613208544"></a>JSON Document</h3>
</div>
</div>
</div>
<p>
        A JSON document is a data structure composed of key-value pairs
        and is the fundamental structure for using MySQL as document
        store. For example, the world_x schema (installed later in this
        chapter) contains this document:
      </p><pre data-lang="json" class="programlisting">
{
    "GNP": .6,
    "IndepYear": 1967,
    "Name": "Sealand",
    "_id": "SEA",
    "demographics": {
        "LifeExpectancy": 79,
        "Population": 27
    },
    "geography": {
        "Continent": "Europe",
        "Region": "British Islands",
        "SurfaceArea": 193
    },
    "government": {
        "GovernmentForm": "Monarchy",
        "HeadOfState": "Michael Bates"
    }
}
</pre><p>
        This document shows that the values of keys can be simple data
        types, such as integers or strings, but can also contain other
        documents, arrays, and lists of documents. For example, the
        <code class="literal">geography</code> key's value consists of multiple
        key-value pairs. A JSON document is represented internally using
        the MySQL binary JSON object, through the
        <a class="link" href="data-types.html#json" title="11.6 The JSON Data Type"><code class="literal">JSON</code></a> MySQL datatype.
      </p><p>
        The most important differences between a document and the tables
        known from traditional relational databases are that the
        structure of a document does not have to be defined in advance,
        and a collection can contain multiple documents with different
        structures. Relational tables on the other hand require that
        their structure be defined, and all rows in the table must
        contain the same columns.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="idm140091613202160"></a>Collection</h3>

</div>

</div>

</div>
<p>
        A collection is a container that is used to store JSON documents
        in a MySQL database. Applications usually run operations against
        a collection of documents, for example to find a specific
        document.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="idm140091613200720"></a>CRUD Operations</h3>

</div>

</div>

</div>
<p>
        The four basic operations that can be issued against a
        collection are Create, Read, Update and Delete (CRUD). In terms
        of MySQL this means:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Create a new document (insertion or addition)
          </p></li><li class="listitem"><p>
            Read one or more documents (queries)
          </p></li><li class="listitem"><p>
            Update one or more documents
          </p></li><li class="listitem"><p>
            Delete one or more documents
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="mysql-shell-tutorial-javascript"></a>20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-download">20.3.1 Import Database Sample</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-shell">20.3.2 MySQL Shell</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-documents-collections">20.3.3 Documents and Collections</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-relational-tables">20.3.4 Relational Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-documents-in-tables">20.3.5 Documents in Tables</a></span></dt></dl>
</div>
<p>
    This quick-start guide provides instructions to begin prototyping
    document store applications interactively with MySQL Shell. The
    guide includes the following topics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Introduction to MySQL functionality, MySQL Shell, and the
        <code class="literal">world_x</code> database sample.
      </p></li><li class="listitem"><p>
        Operations to manage collections and documents.
      </p></li><li class="listitem"><p>
        Operations to manage relational tables.
      </p></li><li class="listitem"><p>
        Operations that apply to documents within tables.
</p></li></ul>
</div>
<h3><a name="idm140091613189200"></a>Related Information</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/" target="_top">MySQL Shell 8.0 (part of MySQL 8.0)</a> provides more in-depth information
        about MySQL Shell.
      </p></li><li class="listitem"><p>
        <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/" target="_top">X DevAPI User Guide</a> provides more examples of
        using X DevAPI.
      </p></li><li class="listitem"><p>
        A <a class="link" href="document-store.html#mysql-shell-tutorial-python" title="20.4 Python Quick-Start Guide: MySQL Shell for Document Store">Python</a>
        quick-start guide is also available.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-javascript-download"></a>20.3.1 Import Database Sample</h3>

</div>

</div>

</div>
<p>
      The <code class="literal">world_x</code> database sample contains one JSON
      collection and a set of three relational tables:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Collection
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              countryinfo: Information about countries in the world.
</p></li></ul>
</div>
</li><li class="listitem"><p>
          Tables
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              country: Minimal information about countries of the world.
            </p></li><li class="listitem"><p>
              city: Information about some of the cities in those
              countries.
            </p></li><li class="listitem"><p>
              countrylanguage: Languages spoken in each country.
</p></li></ul>
</div>
</li></ul>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091613174560"></a>Requirements</h4>

</div>

</div>

</div>
<p>
        To follow this quick-start guide you need a MySQL server with
        X Plugin installed, the default in 8.0, and
        MySQL Shell to use as the client. See
        <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html" target="_top">Installing MySQL Shell</a>. Start MySQL before you
        load the example <code class="literal">world_x</code> database for this
        guide.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091613171696"></a>Download and Import world_x Database</h4>

</div>

</div>

</div>
<p>
        To prepare the <code class="literal">world_x</code> database sample,
        follow these steps:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
            Download
            <a class="ulink" href="http://downloads.mysql.com/docs/world_x-db.zip" target="_top">world_x-db.zip</a>.
          </p></li><li class="listitem"><p>
            Extract the installation archive to a temporary location
            such as <code class="filename">/tmp/</code>. Unpacking the archive
            results in a single file named
            <code class="filename">world_x.sql</code>.
          </p></li><li class="listitem"><p>
            Import the <code class="filename">world_x.sql</code> file to your
            database. You can either:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
                Start MySQL Shell in SQL mode and import the file by
                issuing:
              </p><pre data-lang="terminal" class="programlisting">
<strong class="userinput"><code>mysqlsh -u root --sql --file /tmp/world_x-db/world_x.sql</code></strong>
Enter password: <strong class="userinput"><code>****</code></strong>
</pre></li><li class="listitem"><p>
                Set MySQL Shell to SQL mode while it is running and
                source the schema file while MySQL Shell is running by
                issuing:
              </p><pre data-lang="mysqlsh" class="programlisting">
<strong class="userinput"><code>\source /tmp/world_x-db/world_x.sql</code></strong>
Switching to SQL mode... Commands end with ;
<strong class="userinput"><code>\source /tmp/world_x-db/world_x.sql</code></strong>
</pre></li></ul>
</div>
<p>
            Replace <code class="filename">/tmp/</code> with the path to the
            <code class="filename">world_x.sql</code> file on your system. Enter
            your password when prompted. A non-root account can be used
            as long as the account has privileges to create new
            databases.
</p></li></ol>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091613154160"></a>Related Information</h4>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-sessions.html" target="_top">MySQL Shell Sessions</a> explains session
            types.
          </p></li><li class="listitem"><p>
            See <a class="xref" href="installing.html" title="Chapter 2 Installing and Upgrading MySQL">Chapter 2, <i>Installing and Upgrading MySQL</i></a> for general installation
            assistance.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-javascript-shell"></a>20.3.2 MySQL Shell</h3>

</div>

</div>

</div>
<p>
      MySQL Shell is a unified scripting interface to MySQL Server. It
      supports scripting in JavaScript and Python. JavaScript is the
      default processing mode. In most cases, you need an account to
      connect to the MySQL server instance.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h4 class="title"><a name="mysql-shell-tutorial-javascript-shell-start"></a>Start MySQL Shell</h4>
</div>
</div>
</div>
<p>
        After you have installed and started MySQL server, connect
        MySQL Shell to the server instance. By default, MySQL Shell
        connects using X Protocol. You need to know the address of
        the MySQL server instance you plan to connect to.
      </p><p>
        If MySQL Shell is not already running, open a terminal window
        and issue:
      </p><pre data-lang="terminal" class="programlisting">
<strong class="userinput"><code>mysqlsh <em class="replaceable"><code>name</code></em>@<em class="replaceable"><code>host</code></em>/world_x</code></strong>
</pre><p>
        Alternatively, if MySQL Shell is already running use the
        <code class="literal">\connect</code> command by issuing:
      </p><pre data-lang="mysqlsh" class="programlisting">
<strong class="userinput"><code>\connect <em class="replaceable"><code>name</code></em>@<em class="replaceable"><code>host</code></em>/world_x</code></strong>
</pre><p>
        You need to specify the address of your MySQL server instance
        which you want to connect MySQL Shell to. For example in the
        previous example:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <em class="replaceable"><code>name</code></em> represents the user name of
            your MySQL account.
          </p></li><li class="listitem"><p>
            MySQL Shell prompts you for your password.
          </p></li><li class="listitem"><p>
            The default schema for this session is the
            <code class="literal">world_x</code> database. For instructions on
            setting up the <code class="literal">world_x</code> database sample,
            see
            <a class="xref" href="document-store.html#mysql-shell-tutorial-javascript-download" title="20.3.1 Import Database Sample">Section 20.3.1, “Import Database Sample”</a>.
</p></li></ul>
</div>
<p>
        Once MySQL Shell opens, the <code class="literal">mysql-js&gt;</code>
        prompt indicates that the active language for this session is
        JavaScript.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt;
</pre><p>
        When you run <span class="command"><strong>mysqlsh</strong></span> without the host
        argument, MySQL Shell attempts to connect to the server
        instance running on the localhost interface on port 33060. For
        more information, see <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connections.html" target="_top">MySQL Shell Connections</a>.
      </p><p>
        MySQL Shell supports input-line editing as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <span class="keycap"><strong>left-arrow</strong></span> and <span class="keycap"><strong>right-arrow</strong></span>
            keys move horizontally within the current input line.
          </p></li><li class="listitem"><p>
            <span class="keycap"><strong>up-arrow</strong></span> and <span class="keycap"><strong>down-arrow</strong></span>
            keys move up and down through the set of previously entered
            lines.
          </p></li><li class="listitem"><p>
            <span class="keycap"><strong>Backspace</strong></span> deletes the character before the
            cursor and typing new characters enters them at the cursor
            position.
          </p></li><li class="listitem"><p>
            <span class="keycap"><strong>Enter</strong></span> sends the current input line to the
            server.
</p></li></ul>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="mysql-shell-tutorial-javascript-shell-help"></a>Get Help for MySQL Shell</h4>

</div>

</div>

</div>
<p>
        Type <span class="command"><strong>mysqlsh --help</strong></span> at the prompt of your
        command interpreter for a list of command-line options.
      </p><pre data-lang="terminal" class="programlisting">
<strong class="userinput"><code>mysqlsh --help</code></strong>
</pre><p>
        Type <code class="literal">\help</code> at the MySQL Shell prompt for a
        list of available commands and their descriptions.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>\help</code></strong>
</pre><p>
        Type <code class="literal">\help</code> followed by a command name for
        detailed help about an individual MySQL Shell command. For
        example, to view help on the <code class="literal">\connect</code>
        command, type:
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>\help \connect</code></strong>
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="mysql-shell-tutorial-javascript-shell-quit"></a>Quit MySQL Shell</h4>

</div>

</div>

</div>
<p>
        To quit MySQL Shell, type the following command:
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>\quit</code></strong>
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091613105984"></a>Related Information</h4>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            See <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-interactive-code-execution.html" target="_top">Interactive Code Execution</a>
            for an explanation of how interactive code execution works
            in MySQL Shell.
          </p></li><li class="listitem"><p>
            See <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-getting-started.html" target="_top">Getting Started with MySQL Shell</a> to learn
            about session and connection alternatives.
          </p></li><li class="listitem"><p>
            See
            <a class="xref" href="programs.html#connecting-using-uri-or-key-value-pairs" title="4.2.4 Connecting Using a URI or Key-Value Pairs">Section 4.2.4, “Connecting Using a URI or Key-Value Pairs”</a> to
            learn about specifying the location of a MySQL server.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-javascript-documents-collections"></a>20.3.3 Documents and Collections</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-collections-operations">20.3.3.1 Create, List, and Drop Collections</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-collections-add">20.3.3.2 Add Documents</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-documents-find">20.3.3.3 Find Documents</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-documents-modify">20.3.3.4 Modify Documents</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-documents-remove">20.3.3.5 Remove Documents</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-indexes-create">20.3.3.6 Create and Drop Indexes</a></span></dt></dl>
</div>
<p>
      In MySQL, collections contain JSON documents that you can add,
      find, update, and remove. Collections are containers within a
      schema that you can create, list, and drop.
    </p><p>
      The examples in this section use the countryinfo collection in the
      <code class="literal">world_x</code> database. For instructions on setting
      up the <code class="literal">world_x</code> database sample, see
      <a class="xref" href="document-store.html#mysql-shell-tutorial-javascript-download" title="20.3.1 Import Database Sample">Section 20.3.1, “Import Database Sample”</a>.
</p>
<h4><a name="mysql-shell-tutorial-javascript-documents"></a>Documents</h4>
<p>
      In MySQL, documents are represented as JSON objects. Internally,
      they are stored in an efficient binary format that enables fast
      lookups and updates.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Simple document format for JavaScript:
        </p><pre class="screen">
{field1: "value", field2 : 10, "field 3": null}
</pre></li></ul>
</div>
<p>
      An array of documents consists of a set of documents separated by
      commas and enclosed within <code class="literal">[</code> and
      <code class="literal">]</code> characters.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Simple array of documents for JavaScript:
        </p><pre class="screen">
[{Name: "Aruba", _id: "ABW"}, {Name: "Angola", _id: "AGO"}]
</pre></li></ul>
</div>
<p>
      MySQL supports the following JavaScript value types in JSON
      documents:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          numbers (integer and floating point)
        </p></li><li class="listitem"><p>
          strings
        </p></li><li class="listitem"><p>
          boolean (False and True)
        </p></li><li class="listitem"><p>
          null
        </p></li><li class="listitem"><p>
          arrays of more JSON values
        </p></li><li class="listitem"><p>
          nested (or embedded) objects of more JSON values
</p></li></ul>
</div>
<h4><a name="mysql-shell-tutorial-javascript-collections"></a>Collections</h4>
<p>
      Collections are containers for documents that share a purpose and
      possibly share one or more indexes. Each collection has a unique
      name and exists within a single schema.
    </p><p>
      The term schema is equivalent to a database, which means a group
      of database objects (as opposed to relational schema used to
      enforce structure and constraints over data). A schema does not
      enforce conformity on the documents in a collection.
    </p><p>
      In this quick-start guide:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Basic objects include:
</p>
<div class="informaltable">
<table summary="Objects to use interactively in MySQL Shell"><col width="40%"><col width="60%"><thead><tr>
              <th scope="col">Object form</th>
              <th scope="col">Description</th>
            </tr></thead><tbody><tr>
              <td scope="row"><code class="literal">db</code></td>
              <td><code class="literal">db</code> is a global variable assigned to the current
                active schema. When you want to run operations against
                the schema, for example to retrieve a collection, you
                use methods available for the <code class="literal">db</code>
                variable.</td>
            </tr><tr>
              <td scope="row"><code class="literal">db.getCollections()</code></td>
              <td><a class="link" href="document-store.html#mysql-shell-tutorial-javascript-collections-get" title="List Collections">db.getCollections()</a>
                holds a list of collections in the schema. Use the list
                to get references to collection objects, iterate over
                them, and so on.</td>
</tr></tbody></table>
</div>
</li><li class="listitem"><p>
          Basic operations scoped by collections include:
</p>
<div class="informaltable">
<table summary="CRUD operations available in X DevAPI"><col width="40%"><col width="60%"><thead><tr>
              <th scope="col">Operation form</th>
              <th scope="col">Description</th>
            </tr></thead><tbody><tr>
              <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.add()</code></td>
              <td>The
                <a class="link" href="document-store.html#mysql-shell-tutorial-javascript-collections-add" title="20.3.3.2 Add Documents">add()</a>
                method inserts one document or a list of documents into
                the named collection.</td>
            </tr><tr>
              <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.find()</code></td>
              <td>The
                <a class="link" href="document-store.html#mysql-shell-tutorial-javascript-documents-find" title="20.3.3.3 Find Documents">find()</a>
                method returns some or all documents in the named
                collection.</td>
            </tr><tr>
              <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.modify()</code></td>
              <td>The
                <a class="link" href="document-store.html#mysql-shell-tutorial-javascript-documents-modify" title="20.3.3.4 Modify Documents">modify()</a>
                method updates documents in the named collection.</td>
            </tr><tr>
              <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.remove()</code></td>
              <td>The
                <a class="link" href="document-store.html#mysql-shell-tutorial-javascript-documents-remove" title="20.3.3.5 Remove Documents">remove()</a>
                method deletes one document or a list of documents from
                the named collection.</td>
</tr></tbody></table>
</div>
</li></ul>
</div>
<h4><a name="idm140091613039888"></a>Related Information</h4>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-working-with-collections.html" target="_top">Working with Collections</a>
          for a general overview.
        </p></li><li class="listitem"><p>
          <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/mysql-x-crud-ebnf-definitions.html" target="_top">CRUD EBNF Definitions</a> provides a
          complete list of operations.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-collections-operations"></a>20.3.3.1 Create, List, and Drop Collections</h4>

</div>

</div>

</div>
<p>
        In MySQL Shell, you can create new collections, get a list of
        the existing collections in a schema, and remove an existing
        collection from a schema. Collection names are case-sensitive
        and each collection name must be unique.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091613033664"></a>Confirm the Schema</h5>
</div>
</div>
</div>
<p>
          To show the value that is assigned to the schema variable,
          issue:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db</code></strong>
</pre><p>
          If the schema value is not <code class="literal">Schema:world_x</code>,
          then set the <code class="literal">db</code> variable by issuing:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>\use world_x</code></strong>
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-javascript-collections-create"></a>Create a Collection</h5>

</div>

</div>

</div>
<p>
          To create a new collection in an existing schema, use the
          <code class="literal">db</code> object's
          <code class="literal">createCollection()</code> method. The following
          example creates a collection called <code class="literal">flags</code>
          in the <code class="literal">world_x</code> database.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.createCollection("flags")</code></strong>
</pre><p>
          The method returns a collection object.
</p><pre class="screen">&lt;Collection:flags&gt;</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-javascript-collections-get"></a>List Collections</h5>

</div>

</div>

</div>
<p>
          To display all collections in the <code class="literal">world_x</code>
          database, use the <code class="literal">db</code> object's
          <code class="literal">getCollections()</code> method. Collections
          returned by the server you are currently connected to appear
          between brackets.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.getCollections()</code></strong>
[
    &lt;Collection:countryinfo&gt;,
    &lt;Collection:flags&gt;
]
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-javascript-collections-drop"></a>Drop a Collection</h5>

</div>

</div>

</div>
<p>
          To drop an existing collection from a database, use the
          <code class="literal">db</code> object's
          <code class="literal">dropCollection()</code> method. For example, to
          drop the flags collection from the <code class="literal">world_x</code>
          database, type:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.dropCollection("flags")</code></strong>
</pre><p>
          The <code class="literal">dropCollection()</code> method is also used in
          MySQL Shell to drop a relational table from a database.

</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091613007040"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/collection-objects.html" target="_top">Collection Objects</a> for more
              examples.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-collections-add"></a>20.3.3.2 Add Documents</h4>

</div>

</div>

</div>
<p>
        Use the <code class="literal">add()</code> method to insert one document
        or a list of documents into an existing collection using
        MySQL Shell. All examples in this section use the countryinfo
        collection, which is provided in the <code class="literal">world_x</code>
        schema.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091613000912"></a>Add a Document</h5>
</div>
</div>
</div>
<p>
          Insert the following document into the countryinfo collection.
          As this is multi-line content, press <span class="keycap"><strong>Enter</strong></span>
          twice to insert the document.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.add(
 {
    GNP: .6,
    IndepYear: 1967,
    Name: "Sealand",
    demographics: {
        LifeExpectancy: 79,
        Population: 27
    },
    geography: {
        Continent: "Europe",
        Region: "British Islands",
        SurfaceArea: 193
    },
    government: {
        GovernmentForm: "Monarchy",
        HeadOfState: "Michael Bates"
    }
  }
)</code></strong>
</pre><p>
          The method returns the status of the operation.
        </p><p>
          Each document requires an identifier field called
          <code class="literal">_id</code>. The value of the
          <code class="literal">_id</code> field must be unique among all
          documents in the same collection. In MySQL 8.0.11 and higher,
          document IDs are generated by the server, not the client, so
          MySQL Shell does not automatically set an
          <code class="literal">_id</code> value. A MySQL server at 8.0.11 or
          higher sets an <code class="literal">_id</code> value if the document
          does not contain the <code class="literal">_id</code> field. A MySQL
          server at an earlier 8.0 release or at 5.7 does not set an
          <code class="literal">_id</code> value in this situation, so you must
          specify it explicitly. If you do not, MySQL Shell returns
          error 5115 <span class="errortext">Document is missing a required
          field</span>.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612990800"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-crud-functions.html#crud-ebnf-collectionaddfunction" target="_top">CollectionAddFunction</a> for
              the full syntax definition.
            </p></li><li class="listitem"><p>
              See
              <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html" target="_top">Understanding Document IDs</a>.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-documents-find"></a>20.3.3.3 Find Documents</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">find()</code> method to query for
        and return documents from a collection in a database.
        MySQL Shell provides additional methods to use with the
        <code class="literal">find()</code> method to filter and sort the returned
        documents.
      </p><p>
        MySQL provides the following operators to specify search
        conditions: <code class="literal">OR</code> (<code class="literal">||</code>),
        <code class="literal">AND</code> (<code class="literal">&amp;&amp;</code>),
        <code class="literal">XOR</code>, <code class="literal">IS</code>,
        <code class="literal">NOT</code>, <code class="literal">BETWEEN</code>,
        <code class="literal">IN</code>, <code class="literal">LIKE</code>,
        <code class="literal">!=</code>, <code class="literal">&lt;&gt;</code>,
        <code class="literal">&gt;</code>, <code class="literal">&gt;=</code>,
        <code class="literal">&lt;</code>, <code class="literal">&lt;=</code>,
        <code class="literal">&amp;</code>, <code class="literal">|</code>,
        <code class="literal">&lt;&lt;</code>, <code class="literal">&gt;&gt;</code>,
        <code class="literal">+</code>, <code class="literal">-</code>,
        <code class="literal">*</code>, <code class="literal">/</code>,
        <code class="literal">~</code>, and <code class="literal">%</code>.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-javascript-documents-find-all"></a>Find All Documents in a Collection</h5>
</div>
</div>
</div>
<p>
          To return all documents in a collection, use the
          <code class="literal">find()</code> method without specifying search
          conditions. For example, the following operation returns all
          documents in the countryinfo collection.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find()</code></strong>
[
     {
          "GNP": 828,
          "IndepYear": null,
          "Name": "Aruba",
          "_id": "ABW",
          "demographics": {
              "LifeExpectancy": 78.4000015258789,
              "Population": 103000
          },
          "geography": {
              "Continent": "North America",
              "Region": "Caribbean",
              "SurfaceArea": 193
          },
          "government": {
              "GovernmentForm": "Nonmetropolitan Territory of The Netherlands",
              "HeadOfState": "Beatrix"
          }
          ...
      }
 ]
240 documents in set (0.00 sec)
</pre><p>
          The method produces results that contain operational
          information in addition to all documents in the collection.
        </p><p>
          An empty set (no matching documents) returns the following
          information:
        </p><pre class="screen">
Empty set (0.00 sec) 
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-javascript-documents-find-filter-searches"></a>Filter Searches</h5>

</div>

</div>

</div>
<p>
          You can include search conditions with the
          <code class="literal">find()</code> method. The syntax for expressions
          that form a search condition is the same as that of
          traditional MySQL <a class="xref" href="functions.html" title="Chapter 12 Functions and Operators">Chapter 12, <i>Functions and Operators</i></a>. You must
          enclose all expressions in quotes.
        </p><p>
          All examples in this section use the countryinfo collection in
          the <code class="literal">world_x</code> database. For the sake of
          brevity, some of the examples do not display output.
        </p><p>
          A simple search condition could consist of the
          <code class="literal">Name</code> field and a value we know is in a
          document. The following example returns a single document:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("Name = 'Australia'")</code></strong>
[
    {
        "GNP": 351182,
        "IndepYear": 1901,
        "Name": "Australia",
        "_id": "AUS",
        "demographics": {
            "LifeExpectancy": 79.80000305175781,
            "Population": 18886000
        },
        "geography": {
            "Continent": "Oceania",
            "Region": "Australia and New Zealand",
            "SurfaceArea": 7741220
        },
        "government": {
            "GovernmentForm": "Constitutional Monarchy, Federation",
            "HeadOfState": "Elisabeth II"
        }
    }
]
</pre><p>
          The following example searches for all countries that have a
          GNP higher than $500 billion. The countryinfo collection
          measures GNP in units of million.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("GNP &gt; 500000")</code></strong>
...[<span class="emphasis"><em>output removed</em></span>]
10 documents in set (0.00 sec)
</pre><p>
          The Population field in the following query is embedded within
          the demographics object. To access the embedded field, use a
          period between demographics and Population to identify the
          relationship. Document and field names are case-sensitive.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("GNP &gt; 500000 and demographics.Population &lt; 100000000")</code></strong>
...[<span class="emphasis"><em>output removed</em></span>]
6 documents in set (0.00 sec)
</pre><p>
          Arithmetic operators in the following expression are used to
          query for countries with a GNP per capita higher than $30000.
          Search conditions can include arithmetic operators and most
          MySQL functions.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            Seven documents in the countryinfo collection have a
            population value of zero. Warning messages appear at the end
            of the output.
</p>
</div>
<pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("GNP*1000000/demographics.Population &gt; 30000")</code></strong>
...[<span class="emphasis"><em>output removed</em></span>]
9 documents in set, 7 warnings (0.00 sec)
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
</pre><p>
          You can separate a value from the search condition by using
          the <code class="literal">bind()</code> method. For example, instead of
          specifying a hard-coded country name as the condition,
          substitute a named placeholder consisting of a colon followed
          by a name that begins with a letter, such as
          <span class="emphasis"><em>country</em></span>. Then use the
          <code class="literal">bind(<em class="replaceable"><code>placeholder</code></em>,
          <em class="replaceable"><code>value</code></em>)</code> method as follows:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("Name = :country").bind("country", "Italy")</code></strong>
[
    {
        "GNP": 1161755,
        "IndepYear": 1861,
        "Name": "Italy",
        "_id": "ITA",
        "demographics": {
            "LifeExpectancy": 79,
            "Population": 57680000
        },
        "geography": {
            "Continent": "Europe",
            "Region": "Southern Europe",
            "SurfaceArea": 301316
        },
        "government": {
            "GovernmentForm": "Republic",
            "HeadOfState": "Carlo Azeglio Ciampi"
        }
    }
]
1 document in set (0.01 sec)
</pre>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Tip
</div>
<p>
            Within a program, binding enables you to specify
            placeholders in your expressions, which are filled in with
            values before execution and can benefit from automatic
            escaping, as appropriate.
          </p><p>
            Always use binding to sanitize input. Avoid introducing
            values in queries using string concatenation, which can
            produce invalid input and, in some cases, can cause security
            issues.
</p>
</div>
<p>
          You can use placeholders and the <code class="literal">bind()</code>
          method to create saved searches which you can then call with
          different values. For example to create a saved search for a
          country:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>var myFind = db.countryinfo.find("Name = :country")</code></strong>
mysql-js&gt; <strong class="userinput"><code>myFind.bind('country', 'France')</code></strong>
[
    {
        "GNP": 1424285,
        "IndepYear": 843,
        "Name": "France",
        "_id": "FRA",
        "demographics": {
            "LifeExpectancy": 78.80000305175781,
            "Population": 59225700
        },
        "geography": {
            "Continent": "Europe",
            "Region": "Western Europe",
            "SurfaceArea": 551500
        },
        "government": {
            "GovernmentForm": "Republic",
            "HeadOfState": "Jacques Chirac"
        }
    }
]
1 document in set (0.0028 sec)

mysql-js&gt; <strong class="userinput"><code>myFind.bind('country', 'Germany')</code></strong>
[
    {
        "GNP": 2133367, 
        "IndepYear": 1955, 
        "Name": "Germany", 
        "_id": "DEU", 
        "demographics": {
            "LifeExpectancy": 77.4000015258789, 
            "Population": 82164700
        }, 
        "geography": {
            "Continent": "Europe", 
            "Region": "Western Europe", 
            "SurfaceArea": 357022
        }, 
        "government": {
            "GovernmentForm": "Federal Republic", 
            "HeadOfState": "Johannes Rau"
        }
    }
]
1 document in set (0.0026 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612925712"></a>Project Results</h5>

</div>

</div>

</div>
<p>
          You can return specific fields of a document, instead of
          returning all the fields. The following example returns the
          GNP and Name fields of all documents in the countryinfo
          collection matching the search conditions.
        </p><p>
          Use the <code class="literal">fields()</code> method to pass the list of
          fields to return.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("GNP &gt; 5000000").fields(["GNP", "Name"])</code></strong>
[
    {
        "GNP": 8510700,
        "Name": "United States"
    }
]
1 document in set (0.00 sec)
 </pre><p>
          In addition, you can alter the returned
          documents—adding, renaming, nesting and even computing
          new field values—with an expression that describes the
          document to return. For example, alter the names of the fields
          with the following expression to return only two documents.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find().
fields(mysqlx.expr('{"Name": upper(Name), "GNPPerCapita": GNP*1000000/demographics.Population}')).
limit(2)</code></strong>
[
    {
        "GNPPerCapita": 8038.834951456311,
        "Name": "ARUBA"
    },
    {
        "GNPPerCapita": 263.0281690140845,
        "Name": "AFGHANISTAN"
    }
]
2 documents in set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612918080"></a>Limit, Sort, and Skip Results</h5>

</div>

</div>

</div>
<p>
          You can apply the <code class="literal">limit()</code>,
          <code class="literal">sort()</code>, and <code class="literal">skip()</code>
          methods to manage the number and order of documents returned
          by the <code class="literal">find()</code> method.
        </p><p>
          To specify the number of documents included in a result set,
          append the <code class="literal">limit()</code> method with a value to
          the <code class="literal">find()</code> method. The following query
          returns the first five documents in the countryinfo
          collection.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find().limit(5)</code></strong>
... [<span class="emphasis"><em>output removed</em></span>]
5 documents in set (0.00 sec)
</pre><p>
          To specify an order for the results, append the
          <code class="literal">sort()</code> method to the
          <code class="literal">find()</code> method. Pass to the
          <code class="literal">sort()</code> method a list of one or more fields
          to sort by and, optionally, the descending
          (<code class="literal">desc</code>) or ascending
          (<code class="literal">asc</code>) attribute as appropriate. Ascending
          order is the default order type.
        </p><p>
          For example, the following query sorts all documents by the
          IndepYear field and then returns the first eight documents in
          descending order.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find().sort(["IndepYear desc"]).limit(8)</code></strong>
... [<span class="emphasis"><em>output removed</em></span>]
8 documents in set (0.00 sec)
</pre><p>
          By default, the <code class="literal">limit()</code> method starts from
          the first document in the collection. You can use the
          <code class="literal">skip()</code> method to change the starting
          document. For example, to ignore the first document and return
          the next eight documents matching the condition, pass to the
          <code class="literal">skip()</code> method a value of 1.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find().sort(["IndepYear desc"]).limit(8).skip(1)</code></strong>
... [<span class="emphasis"><em>output removed</em></span>]
8 documents in set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612897552"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              The <a class="link" href="functions.html" title="Chapter 12 Functions and Operators">MySQL Reference
              Manual</a> provides detailed documentation on functions
              and operators.
            </p></li><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-crud-functions.html#crud-ebnf-collectionfindfunction" target="_top">CollectionFindFunction</a> for
              the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-documents-modify"></a>20.3.3.4 Modify Documents</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">modify()</code> method to update one
        or more documents in a collection. The X DevAPI provides
        additional methods for use with the <code class="literal">modify()</code>
        method to:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Set and unset fields within documents.
          </p></li><li class="listitem"><p>
            Append, insert, and delete arrays.
          </p></li><li class="listitem"><p>
            Bind, limit, and sort the documents to be modified.
</p></li></ul>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612886672"></a>Set and Unset Fields</h5>

</div>

</div>

</div>
<p>
          The <code class="literal">modify()</code> method works by filtering a
          collection to include only the documents to be modified and
          then applying the operations that you specify to those
          documents.
        </p><p>
          In the following example, the <code class="literal">modify()</code>
          method uses the search condition to identify the document to
          change and then the <code class="literal">set()</code> method replaces
          two values within the nested demographics object.

          
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.modify("_id = 'SEA'").\
set("demographics", {LifeExpectancy: 78, Population: 28})</code></strong>
Query OK, 1 item affected (0.04 sec)
</pre><p>
          After you modify a document, use the <code class="literal">find()</code>
          method to verify the change.
        </p><p>
          To remove content from a document, use the
          <code class="literal">modify()</code> and <code class="literal">unset()</code>
          methods. For example, the following query removes the GNP from
          a document that matches the search condition.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.modify("Name = 'Sealand'").unset("GNP")</code></strong>
Query OK, 1 item affected (0.01 sec)   
</pre><p>
          Use the <code class="literal">find()</code> method to verify the change.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("Name = 'Sealand'")</code></strong>
[
    {
        "IndepYear": 1967,
        "Name": "Sealand",
        "_id": "SEA",
        "demographics": {
            "LifeExpectancy": 78,
            "Population": 28
        },
        "geography": {
            "Continent": "Europe",
            "Region": "British Islands",
            "SurfaceArea": 193
        },
        "government": {
            "GovernmentForm": "Monarchy",
            "HeadOfState": "Michael Bates"
        }
    }
]
1 document in set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612871584"></a>Append, Insert, and Delete Arrays</h5>

</div>

</div>

</div>
<p>
          To append an element to an array field, or insert, or delete
          elements in an array, use the
          <code class="literal">arrayAppend()</code>,
          <code class="literal">arrayInsert()</code>, or
          <code class="literal">arrayDelete()</code> methods. The following
          examples modify the countryinfo collection to enable tracking
          of international airports.
        </p><p>
          The first example uses the <code class="literal">modify()</code> and
          <code class="literal">set()</code> methods to create a new Airports
          field in all documents.
</p>
<div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Caution
</div>
<p>
            Use care when you modify documents without specifying a
            search condition. This action will modify all documents in
            the collection.
</p>
</div>
<pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.modify("true").set("Airports", [])</code></strong>
Query OK, 240 items affected (0.07 sec)
</pre><p>
          With the Airports field added, the next example uses the
          <code class="literal">arrayAppend()</code> method to add a new airport
          to one of the documents. <span class="emphasis"><em>$.Airports</em></span> in
          the following example represents the Airports field of the
          current document.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.modify("Name = 'France'").arrayAppend("$.Airports", "ORY")</code></strong>
Query OK, 1 item affected (0.02 sec)
</pre><p>
          Use <strong class="userinput"><code>db.countryinfo.find("Name =
          'France'")</code></strong> to see the change.
        </p><p>
          To insert an element at a different position in the array, use
          the <code class="literal">arrayInsert()</code> method to specify which
          index to insert in the path expression. In this case, the
          index is 0, or the first element in the array.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.modify("Name = 'France'").arrayInsert("$.Airports[0]", "CDG")</code></strong>
Query OK, 1 item affected (0.04 sec)
</pre><p>
          To delete an element from the array, you must pass to the
          <code class="literal">arrayDelete()</code> method the index of the
          element to be deleted.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.modify("Name = 'France'").arrayDelete("$.Airports[1]")</code></strong>
Query OK, 1 item affected (0.03 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612852464"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              The <a class="link" href="data-types.html#json-paths" title="Searching and Modifying JSON Values">MySQL Reference
              Manual</a> provides instructions to help you search for
              and modify JSON values.
            </p></li><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-crud-functions.html#crud-ebnf-collectionmodifyfunction" target="_top">CollectionModifyFunction</a>
              for the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-documents-remove"></a>20.3.3.5 Remove Documents</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">remove()</code> method to delete
        some or all documents from a collection in a database. The
        X DevAPI provides additional methods for use with the
        <code class="literal">remove()</code> method to filter and sort the
        documents to be removed.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612844432"></a>Remove Documents Using Conditions</h5>
</div>
</div>
</div>
<p>
          The example that follows passes a search condition to the
          <code class="literal">remove()</code> method. All documents matching the
          condition will be removed from the countryinfo collection. In
          this example, one document matches the condition.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.remove("_id = 'SEA'")</code></strong>
Query OK, 1 item affected (0.02 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612840544"></a>Remove the First Document</h5>

</div>

</div>

</div>
<p>
          To remove the first document in the countryinfo collection,
          use the <code class="literal">limit()</code> method with a value of 1.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.remove("true").limit(1)</code></strong>
Query OK, 1 item affected (0.03 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612836784"></a>Remove the Last Document in an Order</h5>

</div>

</div>

</div>
<p>
          The following example removes the last document in the
          countryinfo collection by country name.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.remove("true").sort(["Name desc"]).limit(1)</code></strong>
Query OK, 1 item affected (0.02 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612833696"></a>Remove All Documents in a Collection</h5>

</div>

</div>

</div>
<p>
          You can remove all documents in a collection. To do so, use
          the <code class="literal">remove("true")</code> method without
          specifying any search condition.
</p>
<div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Caution
</div>
<p>
            Use care when you remove documents without specifying a
            search condition. This action will delete all documents from
            the collection.
</p>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612830672"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-crud-functions.html#crud-ebnf-collectionremovefunction" target="_top">CollectionRemoveFunction</a>
              for the full syntax definition.
            </p></li><li class="listitem"><p>
              See
              <a class="xref" href="document-store.html#mysql-shell-tutorial-javascript-download" title="20.3.1 Import Database Sample">Section 20.3.1, “Import Database Sample”</a>
              for instructions to recreate the
              <code class="literal">world_x</code> database.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-indexes-create"></a>20.3.3.6 Create and Drop Indexes</h4>

</div>

</div>

</div>
<p>
        Indexes are used to find documents with specific field values
        quickly. Without an index, MySQL must begin with the first
        document and then read through the entire collection to find the
        relevant fields. The larger the collection, the more this costs.
        If a collection is large and queries on a specific field are
        common, then consider creating an index on a specific field
        inside a document.
      </p><p>
        For example, the following query will perform better with an
        index:
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("demographics.Population &lt; 100")</code></strong>
...[<span class="emphasis"><em>output removed</em></span>]
8 documents in set (0.00 sec)
</pre><p>
        The <code class="literal">createIndex()</code> method creates an index
        that you can define as nonunique or unique. Use the
        <code class="literal">field()</code> method to chain the fields that
        should be indexed. The <code class="literal">execute()</code> method is
        required to create or drop an index.
      </p><p>
        In MySQL, the <code class="literal">_id</code> field is equivalent to a
        primary key by default.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612816192"></a>Add a Nonunique Index</h5>
</div>
</div>
</div>
<p>
          To create a nonunique index, pass to the
          <code class="literal">createIndex()</code> method an index name.
          Duplicate index names are prohibited.
        </p><p>
          In the following example, the first parameter of the
          <code class="literal">field()</code> method specifies the Population
          field inside the demographics object and the next parameter
          indicates that the field should be indexed as an Integer
          numeric value. The last parameter indicates whether the field
          should require the NOT NULL constraint. If the value is
          <code class="literal">false</code>, the field can contain
          <code class="literal">NULL</code> values.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.createIndex("pop").
field("demographics.Population", "INTEGER", false).execute()</code></strong>
Query OK (0.04 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612809424"></a>Add a Unique Index</h5>

</div>

</div>

</div>
<p>
          To create a unique index, pass to the
          <code class="literal">createIndex()</code> method an index name and the
          <code class="literal">mysqlx.IndexType.UNIQUE</code> type. Country
          <code class="literal">"Name"</code> is another common field in the
          countryinfo collection to index. In the following example,
          <code class="literal">"Text(40)"</code> represents the number of
          characters to index and <code class="literal">true</code> indicates that
          the field cannot contain any <code class="literal">NULL</code> values.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.createIndex("name", mysqlx.IndexType.UNIQUE).
field("Name", "TEXT(40)", true).execute()</code></strong>
Query OK (0.04 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612801920"></a>Drop an Index</h5>

</div>

</div>

</div>
<p>
          To drop an index, pass to the <code class="literal">dropIndex()</code>
          method the name of the index to drop. For example, you can
          drop the <span class="quote">“<span class="quote">pop</span>”</span> index as follows:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.dropIndex("pop").execute()</code></strong>
Query OK (0.58 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612797680"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See
              <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-index-management-functions.html" target="_top">Collection Index Management Functions</a>
              for the full syntax definition.

              
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-javascript-relational-tables"></a>20.3.4 Relational Tables</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-table-insert">20.3.4.1 Insert Records into Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-table-select">20.3.4.2 Select Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-table-update">20.3.4.3 Update Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-javascript-table-delete">20.3.4.4 Delete Tables</a></span></dt></dl>
</div>
<p>
      You can use MySQL Shell to manipulate not just JSON documents,
      but also relational tables.
    </p><p>
      In MySQL, each relational table is associated with a particular
      storage engine. The examples in this section use
      <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> tables in the
      <code class="literal">world_x</code> database.
</p>
<h4><a name="idm140091612789536"></a>Confirm the Schema</h4>
<p>
      To show the value that is assigned to the schema variable, type
      <code class="literal">db</code>.
    </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db</code></strong>
</pre><p>
      If the schema value is not <code class="literal">Schema:world_x</code>, then
      set the <code class="literal">db</code> variable as follows:
    </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>\use world_x</code></strong>
</pre>
<h4><a name="idm140091612782560"></a>Show All Tables</h4>
<p>
      To display all relational tables in the <code class="literal">world_x</code>
      database, use the <code class="literal">getTables()</code> method on the
      schema object.
    </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.getTables()</code></strong>
{
    "city": &lt;Table:city&gt;,
    "country": &lt;Table:country&gt;,
    "countrylanguage": &lt;Table:countrylanguage&gt;
}
</pre>
<h4><a name="idm140091612778096"></a>Basic Table Operations</h4>
<p>
      Basic operations scoped by tables include:
</p>
<div class="informaltable">
<table summary="CRUD operations to use interactively on tables within MySQL Shell"><col width="32%"><col width="68%"><thead><tr>
          <th scope="col">Operation form</th>
          <th scope="col">Description</th>
        </tr></thead><tbody><tr>
          <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.insert()</code></td>
          <td>The
            <a class="link" href="document-store.html#mysql-shell-tutorial-javascript-table-insert" title="20.3.4.1 Insert Records into Tables">insert()</a>
            method inserts one or more records into the named table.</td>
        </tr><tr>
          <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.select()</code></td>
          <td>The
            <a class="link" href="document-store.html#mysql-shell-tutorial-javascript-table-select" title="20.3.4.2 Select Tables">select()</a>
            method returns some or all records in the named table.</td>
        </tr><tr>
          <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.update()</code></td>
          <td>The
            <a class="link" href="document-store.html#mysql-shell-tutorial-javascript-table-update" title="20.3.4.3 Update Tables">update()</a>
            method updates records in the named table.</td>
        </tr><tr>
          <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.delete()</code></td>
          <td>The
            <a class="link" href="document-store.html#mysql-shell-tutorial-javascript-table-delete" title="20.3.4.4 Delete Tables">delete()</a>
            method deletes one or more records from the named table.</td>
</tr></tbody></table>
</div>
<h4><a name="idm140091612753968"></a>Related Information</h4>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          See
          <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-working-with-relational-tables.html" target="_top">Working with Relational Tables</a>
          for a general overview.
        </p></li><li class="listitem"><p>
          <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/mysql-x-crud-ebnf-definitions.html" target="_top">CRUD EBNF Definitions</a> provides a
          complete list of operations.
        </p></li><li class="listitem"><p>
          See <a class="xref" href="document-store.html#mysql-shell-tutorial-javascript-download" title="20.3.1 Import Database Sample">Section 20.3.1, “Import Database Sample”</a>
          for instructions on setting up the <code class="literal">world_x</code>
          database sample.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-table-insert"></a>20.3.4.1 Insert Records into Tables</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">insert()</code> method with the
        <code class="literal">values()</code> method to insert records into an
        existing relational table. The <code class="literal">insert()</code>
        method accepts individual columns or all columns in the table.
        Use one or more <code class="literal">values()</code> methods to specify
        the values to be inserted.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612742624"></a>Insert a Complete Record</h5>
</div>
</div>
</div>
<p>
          To insert a complete record, pass to the
          <code class="literal">insert()</code> method all columns in the table.
          Then pass to the <code class="literal">values()</code> method one value
          for each column in the table. For example, to add a new record
          to the city table in the <code class="literal">world_x</code> database,
          insert the following record and press <span class="keycap"><strong>Enter</strong></span>
          twice.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.insert("ID", "Name", "CountryCode", "District", "Info").
values(null, "Olympia", "USA", "Washington", '{"Population": 5000}')</code></strong>
Query OK, 1 item affected (0.01 sec)
</pre><p>
          The city table has five columns: ID, Name, CountryCode,
          District, and Info. Each value must match the data type of the
          column it represents.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612735856"></a>Insert a Partial Record</h5>

</div>

</div>

</div>
<p>
          The following example inserts values into the ID, Name, and
          CountryCode columns of the city table.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.insert("ID", "Name", "CountryCode").
values(null, "Little Falls", "USA").values(null, "Happy Valley", "USA")</code></strong>
Query OK, 2 item affected (0.03 sec)
</pre><p>
          When you specify columns using the <code class="literal">insert()</code>
          method, the number of values must match the number of columns.
          In the previous example, you must supply three values to match
          the three columns specified.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612731456"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html#crud-ebnf-tableinsertfunction" target="_top">TableInsertFunction</a> for
              the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-table-select"></a>20.3.4.2 Select Tables</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">select()</code> method to query for
        and return records from a table in a database. The
        X DevAPI provides additional methods to use with the
        <code class="literal">select()</code> method to filter and sort the
        returned records.
      </p><p>
        MySQL provides the following operators to specify search
        conditions: <code class="literal">OR</code> (<code class="literal">||</code>),
        <code class="literal">AND</code> (<code class="literal">&amp;&amp;</code>),
        <code class="literal">XOR</code>, <code class="literal">IS</code>,
        <code class="literal">NOT</code>, <code class="literal">BETWEEN</code>,
        <code class="literal">IN</code>, <code class="literal">LIKE</code>,
        <code class="literal">!=</code>, <code class="literal">&lt;&gt;</code>,
        <code class="literal">&gt;</code>, <code class="literal">&gt;=</code>,
        <code class="literal">&lt;</code>, <code class="literal">&lt;=</code>,
        <code class="literal">&amp;</code>, <code class="literal">|</code>,
        <code class="literal">&lt;&lt;</code>, <code class="literal">&gt;&gt;</code>,
        <code class="literal">+</code>, <code class="literal">-</code>,
        <code class="literal">*</code>, <code class="literal">/</code>,
        <code class="literal">~</code>, and <code class="literal">%</code>.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612706864"></a>Select All Records</h5>
</div>
</div>
</div>
<p>
          To issue a query that returns all records from an existing
          table, use the <code class="literal">select()</code> method without
          specifying search conditions. The following example selects
          all records from the city table in the
          <code class="literal">world_x</code> database.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            Limit the use of the empty <code class="literal">select()</code>
            method to interactive statements. Always use explicit
            column-name selections in your application code.
</p>
</div>
<pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.select()</code></strong>
+------+------------+-------------+------------+-------------------------+
| ID   | Name       | CountryCode | District   | Info                    |
+------+------------+-------------+------------+-------------------------+
|    1 | Kabul      | AFG         | Kabol      |{"Population": 1780000}  |
|    2 | Qandahar   | AFG         | Qandahar   |{"Population": 237500}   |
|    3 | Herat      | AFG         | Herat      |{"Population": 186800}   |
...    ...          ...           ...          ...
| 4079 | Rafah      | PSE         | Rafah      |{"Population": 92020}    |
+------+------- ----+-------------+------------+-------------------------+
4082 rows in set (0.01 sec)
</pre><p>
          An empty set (no matching records) returns the following
          information:
        </p><pre class="screen">
Empty set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612698848"></a>Filter Searches</h5>

</div>

</div>

</div>
<p>
          To issue a query that returns a set of table columns, use the
          <code class="literal">select()</code> method and specify the columns to
          return between square brackets. This query returns the Name
          and CountryCode columns from the city table.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"])</code></strong>
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Kabul             | AFG         |
| Qandahar          | AFG         |
| Herat             | AFG         |
| Mazar-e-Sharif    | AFG         |
| Amsterdam         | NLD         |
...                 ...
| Rafah             | PSE         |
| Olympia           | USA         |
| Little Falls      | USA         |
| Happy Valley      | USA         |
+-------------------+-------------+
4082 rows in set (0.00 sec)
</pre><p>
          To issue a query that returns rows matching specific search
          conditions, use the <code class="literal">where()</code> method to
          include those conditions. For example, the following example
          returns the names and country codes of the cities that start
          with the letter Z.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"]).where("Name like 'Z%'")</code></strong>
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zaanstad          | NLD         |
| Zoetermeer        | NLD         |
| Zwolle            | NLD         |
| Zenica            | BIH         |
| Zagazig           | EGY         |
| Zaragoza          | ESP         |
| Zamboanga         | PHL         |
| Zahedan           | IRN         |
| Zanjan            | IRN         |
| Zabol             | IRN         |
| Zama              | JPN         |
| Zhezqazghan       | KAZ         |
| Zhengzhou         | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
59 rows in set (0.00 sec)
</pre><p>
          You can separate a value from the search condition by using
          the <code class="literal">bind()</code> method. For example, instead of
          using "Name = 'Z%' " as the condition, substitute a named
          placeholder consisting of a colon followed by a name that
          begins with a letter, such as <span class="emphasis"><em>name</em></span>. Then
          include the placeholder and value in the
          <code class="literal">bind()</code> method as follows:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"]).
              where("Name like :name").bind("name", "Z%")</code></strong>
</pre>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Tip
</div>
<p>
            Within a program, binding enables you to specify
            placeholders in your expressions, which are filled in with
            values before execution and can benefit from automatic
            escaping, as appropriate.
          </p><p>
            Always use binding to sanitize input. Avoid introducing
            values in queries using string concatenation, which can
            produce invalid input and, in some cases, can cause security
            issues.
</p>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612684640"></a>Project Results</h5>

</div>

</div>

</div>
<p>
          To issue a query using the <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a>
          operator, add the operator between search conditions in the
          <code class="literal">where()</code> method.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"]).
               where("Name like 'Z%' and CountryCode = 'CHN'")</code></strong>
+----------------+-------------+
| Name           | CountryCode |
+----------------+-------------+
| Zhengzhou      | CHN         |
| Zibo           | CHN         |
| Zhangjiakou    | CHN         |
| Zhuzhou        | CHN         |
| Zhangjiang     | CHN         |
| Zigong         | CHN         |
| Zaozhuang      | CHN         |
...              ...
| Zhangjiagang   | CHN         |
+----------------+-------------+
22 rows in set (0.01 sec)
</pre><p>
          To specify multiple conditional operators, you can enclose the
          search conditions in parenthesis to change the operator
          precedence. The following example demonstrates the placement
          of <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a> and
          <a class="link" href="functions.html#operator_or"><code class="literal">OR</code></a> operators.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"]).
where("Name like 'Z%' and (CountryCode = 'CHN' or CountryCode = 'RUS')")</code></strong>
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zhengzhou         | CHN         |
| Zibo              | CHN         |
| Zhangjiakou       | CHN         |
| Zhuzhou           | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
29 rows in set (0.01 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612673632"></a>Limit, Order, and Offset Results</h5>

</div>

</div>

</div>
<p>
          You can apply the <code class="literal">limit()</code>,
          <code class="literal">orderBy()</code>, and <code class="literal">offSet()</code>
          methods to manage the number and order of records returned by
          the <code class="literal">select()</code> method.
        </p><p>
          To specify the number of records included in a result set,
          append the <code class="literal">limit()</code> method with a value to
          the <code class="literal">select()</code> method. For example, the
          following query returns the first five records in the country
          table.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.country.select(["Code", "Name"]).limit(5)</code></strong>
+------+-------------+
| Code | Name        |
+------+-------------+
| ABW  | Aruba       |
| AFG  | Afghanistan |
| AGO  | Angola      |
| AIA  | Anguilla    |
| ALB  | Albania     |
+------+-------------+
5 rows in set (0.00 sec)
</pre><p>
          To specify an order for the results, append the
          <code class="literal">orderBy()</code> method to the
          <code class="literal">select()</code> method. Pass to the
          <code class="literal">orderBy()</code> method a list of one or more
          columns to sort by and, optionally, the descending
          (<code class="literal">desc</code>) or ascending
          (<code class="literal">asc</code>) attribute as appropriate. Ascending
          order is the default order type.
        </p><p>
          For example, the following query sorts all records by the Name
          column and then returns the first three records in descending
          order .
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.country.select(["Code", "Name"]).orderBy(["Name desc"]).limit(3)</code></strong>
+------+------------+
| Code | Name       |
+------+------------+
| ZWE  | Zimbabwe   |
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
+------+------------+
3 rows in set (0.00 sec)
</pre><p>
          By default, the <code class="literal">limit()</code> method starts from
          the first record in the table. You can use the
          <code class="literal">offset()</code> method to change the starting
          record. For example, to ignore the first record and return the
          next three records matching the condition, pass to the
          <code class="literal">offset()</code> method a value of 1.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.country.select(["Code", "Name"]).orderBy(["Name desc"]).limit(3).offset(1)</code></strong>
+------+------------+
| Code | Name       |
+------+------------+
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
| YEM  | Yemen      |
+------+------------+
3 rows in set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612654000"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              The <a class="link" href="functions.html" title="Chapter 12 Functions and Operators">MySQL Reference
              Manual</a> provides detailed documentation on functions
              and operators.
            </p></li><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html#crud-ebnf-tableselectfunction" target="_top">TableSelectFunction</a> for
              the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-table-update"></a>20.3.4.3 Update Tables</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">update()</code> method to modify one
        or more records in a table. The <code class="literal">update()</code>
        method works by filtering a query to include only the records to
        be updated and then applying the operations you specify to those
        records.
      </p><p>
        To replace a city name in the city table, pass to the
        <code class="literal">set()</code> method the new city name. Then, pass to
        the <code class="literal">where()</code> method the city name to locate
        and replace. The following example replaces the city Peking with
        Beijing.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.update().set("Name", "Beijing").where("Name = 'Peking'")</code></strong>
Query OK, 1 item affected (0.04 sec)
</pre><p>
        Use the <code class="literal">select()</code> method to verify the change.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).where("Name = 'Beijing'")</code></strong>
+------+-----------+-------------+----------+-----------------------------+
| ID   | Name      | CountryCode | District | Info                        |
+------+-----------+-------------+----------+-----------------------------+
| 1891 | Beijing   | CHN         | Peking   | {"Population": 7472000}     |
+------+-----------+-------------+----------+-----------------------------+
1 row in set (0.00 sec)
</pre>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612638976"></a>Related Information</h5>
</div>
</div>
</div>

<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html#crud-ebnf-tableupdatefunction" target="_top">TableUpdateFunction</a> for
              the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-javascript-table-delete"></a>20.3.4.4 Delete Tables</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">delete()</code> method to remove
        some or all records from a table in a database. The
        X DevAPI provides additional methods to use with the
        <code class="literal">delete()</code> method to filter and order the
        records to be deleted.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612632720"></a>Delete Records Using Conditions</h5>
</div>
</div>
</div>
<p>
          The example that follows passes search conditions to the
          <code class="literal">delete()</code> method. All records matching the
          condition will be deleted from the city table. In this
          example, one record matches the condition.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.delete().where("Name = 'Olympia'")</code></strong>
Query OK, 1 item affected (0.01 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612628896"></a>Delete the First Record</h5>

</div>

</div>

</div>
<p>
          To delete the first record in the city table, use the
          <code class="literal">limit()</code> method with a value of 1.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.delete().limit(1)</code></strong>
Query OK, 1 item affected (0.02 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612625168"></a>Delete All Records in a Table</h5>

</div>

</div>

</div>
<p>
          You can delete all records in a table. To do so, use the
          <code class="literal">delete()</code> method without specifying a search
          condition.
</p>
<div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Caution
</div>
<p>
            Use care when you delete records without specifying a search
            condition. This action will delete all records from the
            table.
</p>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612622144"></a>Drop a Table</h5>

</div>

</div>

</div>
<p>
          The <code class="literal">dropCollection()</code> method is also used in
          MySQL Shell to drop a relational table from a database. For
          example, to drop the citytest table from the
          <code class="literal">world_x</code> database, type:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>session.dropCollection("world_x", "citytest")</code></strong>
Query OK (0.04 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612617616"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html#crud-ebnf-tabledeletefunction" target="_top">TableDeleteFunction</a> for
              the full syntax definition.
            </p></li><li class="listitem"><p>
              See
              <a class="xref" href="document-store.html#mysql-shell-tutorial-javascript-download" title="20.3.1 Import Database Sample">Section 20.3.1, “Import Database Sample”</a>
              for instructions to recreate the
              <code class="literal">world_x</code> database.
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-javascript-documents-in-tables"></a>20.3.5 Documents in Tables</h3>

</div>

</div>

</div>
<p>
      In MySQL, a table may contain traditional relational data, JSON
      values, or both. You can combine traditional data with JSON
      documents by storing the documents in columns having a native
      <a class="link" href="data-types.html#json" title="11.6 The JSON Data Type"><code class="literal">JSON</code></a> data type.
    </p><p>
      Examples in this section use the city table in the
      <code class="literal">world_x</code> database.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h4 class="title"><a name="idm140091612607888"></a>city Table Description</h4>
</div>
</div>
</div>
<p>
        The city table has five columns (or fields).
      </p><pre class="screen">
+---------------+------------+-------+-------+---------+------------------+
| Field         | Type       | Null  | Key   | Default | Extra            |
+---------------+------------+-------+-------+---------+------------------+
| ID            | int(11)    | NO    | PRI   | null    | auto_increment   |
| Name          | char(35)   | NO    |       |         |                  |
| CountryCode   | char(3)    | NO    |       |         |                  |
| District      | char(20)   | NO    |       |         |                  |
| Info          | json       | YES   |       | null    |                  |
+---------------+------------+-------+-------+---------+------------------+
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091612604576"></a>Insert a Record</h4>

</div>

</div>

</div>
<p>
        To insert a document into the column of a table, pass to the
        <code class="literal">values()</code> method a well-formed JSON document
        in the correct order. In the following example, a document is
        passed as the final value to be inserted into the Info column.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.insert().
values(null, "San Francisco", "USA", "California", '{"Population":830000}')</code></strong>
Query OK, 1 item affected (0.01 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091612600640"></a>Select a Record</h4>

</div>

</div>

</div>
<p>
        You can issue a query with a search condition that evaluates
        document values in the expression.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).
  where("CountryCode = :country and Info-&gt;'$.Population' &gt; 1000000").
  bind('country', 'USA')</code></strong>
+------+----------------+-------------+----------------+-----------------------------+
| ID   | Name           | CountryCode | District       | Info                        |
+------+----------------+-------------+----------------+-----------------------------+
| 3793 | New York       | USA         | New York       | {"Population": 8008278}     |
| 3794 | Los Angeles    | USA         | California     | {"Population": 3694820}     |
| 3795 | Chicago        | USA         | Illinois       | {"Population": 2896016}     |
| 3796 | Houston        | USA         | Texas          | {"Population": 1953631}     |
| 3797 | Philadelphia   | USA         | Pennsylvania   | {"Population": 1517550}     |
| 3798 | Phoenix        | USA         | Arizona        | {"Population": 1321045}     |
| 3799 | San Diego      | USA         | California     | {"Population": 1223400}     |
| 3800 | Dallas         | USA         | Texas          | {"Population": 1188580}     |
| 3801 | San Antonio    | USA         | Texas          | {"Population": 1144646}     |
+------+----------------+-------------+----------------+-----------------------------+
9 rows in set (0.01 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091612596304"></a>Related Information</h4>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            See
            <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-working-with-relational-tables-and-documents.html" target="_top">Working with Relational Tables and Documents</a>
            for a general overview.
          </p></li><li class="listitem"><p>
            See <a class="xref" href="data-types.html#json" title="11.6 The JSON Data Type">Section 11.6, “The JSON Data Type”</a> for a detailed description of the
            data type.
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="mysql-shell-tutorial-python"></a>20.4 Python Quick-Start Guide: MySQL Shell for Document Store</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-download">20.4.1 Import Database Sample</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-shell">20.4.2 MySQL Shell</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-collections">20.4.3 Documents and Collections</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-relational-tables">20.4.4 Relational Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-in-tables">20.4.5 Documents in Tables</a></span></dt></dl>
</div>
<p>
    This quick-start guide provides instructions to begin prototyping
    document store applications interactively with MySQL Shell. The
    guide includes the following topics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Introduction to MySQL functionality, MySQL Shell, and the
        <code class="literal">world_x</code> database sample.
      </p></li><li class="listitem"><p>
        Operations to manage collections and documents.
      </p></li><li class="listitem"><p>
        Operations to manage relational tables.
      </p></li><li class="listitem"><p>
        Operations that apply to documents within tables.
</p></li></ul>
</div>
<h3><a name="idm140091612585440"></a>Related Information</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/" target="_top">MySQL Shell 8.0 (part of MySQL 8.0)</a> provides more in-depth information
        about MySQL Shell.
      </p></li><li class="listitem"><p>
        <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/" target="_top">X DevAPI User Guide</a> provides more examples of
        using X DevAPI.
      </p></li><li class="listitem"><p>
        A
        <a class="link" href="document-store.html#mysql-shell-tutorial-javascript" title="20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store">JavaScript</a>
        quick-start guide is also available.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-python-download"></a>20.4.1 Import Database Sample</h3>

</div>

</div>

</div>
<p>
      The <code class="literal">world_x</code> database sample contains one JSON
      collection and a set of three relational tables:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Collection
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              countryinfo: Information about countries in the world.
</p></li></ul>
</div>
</li><li class="listitem"><p>
          Tables
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              country: Minimal information about countries of the world.
            </p></li><li class="listitem"><p>
              city: Information about some of the cities in those
              countries.
            </p></li><li class="listitem"><p>
              countrylanguage: Languages spoken in each country.
</p></li></ul>
</div>
</li></ul>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091612570848"></a>Requirements</h4>

</div>

</div>

</div>
<p>
        To follow this quick-start guide you need a MySQL server with
        X Plugin installed, the default in 8.0, and
        MySQL Shell to use as the client. See
        <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html" target="_top">Installing MySQL Shell</a>. Start MySQL before you
        load the example <code class="literal">world_x</code> database for this
        guide.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091612567920"></a>Download and Import world_x Database</h4>

</div>

</div>

</div>
<p>
        To prepare the <code class="literal">world_x</code> database sample,
        follow these steps:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
            Download
            <a class="ulink" href="http://downloads.mysql.com/docs/world_x-db.zip" target="_top">world_x-db.zip</a>.
          </p></li><li class="listitem"><p>
            Extract the installation archive to a temporary location
            such as <code class="filename">/tmp/</code>. Unpacking the archive
            results in a single file named
            <code class="filename">world_x.sql</code>.
          </p></li><li class="listitem"><p>
            Import the <code class="filename">world_x.sql</code> file to your
            database. You can either:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
                Start MySQL Shell in SQL mode and import the file by
                issuing:
              </p><pre data-lang="terminal" class="programlisting">
<strong class="userinput"><code>mysqlsh -u root --sql --file /tmp/world_x-db/world_x.sql</code></strong>
Enter password: <strong class="userinput"><code>****</code></strong>
</pre></li><li class="listitem"><p>
                Set MySQL Shell to SQL mode while it is running and
                source the schema file while MySQL Shell is running by
                issuing:
              </p><pre data-lang="mysqlsh" class="programlisting">
<strong class="userinput"><code>\source /tmp/world_x-db/world_x.sql</code></strong>
Switching to SQL mode... Commands end with ;
<strong class="userinput"><code>\source /tmp/world_x-db/world_x.sql</code></strong>
</pre></li></ul>
</div>
<p>
            Replace <code class="filename">/tmp/</code> with the path to the
            <code class="filename">world_x.sql</code> file on your system. Enter
            your password when prompted. A non-root account can be used
            as long as the account has privileges to create new
            databases.
</p></li></ol>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091612550384"></a>Related Information</h4>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-sessions.html" target="_top">MySQL Shell Sessions</a> explains session
            types.
          </p></li><li class="listitem"><p>
            See <a class="xref" href="installing.html" title="Chapter 2 Installing and Upgrading MySQL">Chapter 2, <i>Installing and Upgrading MySQL</i></a> for general installation
            assistance.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-python-shell"></a>20.4.2 MySQL Shell</h3>

</div>

</div>

</div>
<p>
      MySQL Shell is a unified scripting interface to MySQL Server. It
      supports scripting in JavaScript and Python. JavaScript is the
      default processing mode. In most cases, you need an account to
      connect to the MySQL server instance.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h4 class="title"><a name="mysql-shell-tutorial-python-shell-start"></a>Start MySQL Shell</h4>
</div>
</div>
</div>
<p>
        After you have installed and started MySQL server, connect
        MySQL Shell to the server instance. By default, MySQL Shell
        connects using X Protocol. You need to know the address of
        the MySQL server instance you plan to connect to.
      </p><p>
        If MySQL Shell is not already running, open a terminal window
        and issue:
      </p><pre data-lang="terminal" class="programlisting">
<strong class="userinput"><code>mysqlsh <em class="replaceable"><code>name</code></em>@localhost/world_x</code></strong> --py
</pre><p>
        Alternatively, if MySQL Shell is already running use the
        <code class="literal">\connect</code> command by issuing:
      </p><pre data-lang="mysqlsh" class="programlisting">
<strong class="userinput"><code>\connect <em class="replaceable"><code>name</code></em>@<em class="replaceable"><code>host</code></em>/world_x</code></strong>
</pre><p>
        You need to specify the address of your MySQL server instance
        which you want to connect MySQL Shell to. For example in the
        previous example:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <em class="replaceable"><code>name</code></em> represents the user name of
            your MySQL account.
          </p></li><li class="listitem"><p>
            MySQL Shell prompts you for your password.
          </p></li><li class="listitem"><p>
            The <code class="literal">--py</code> option starts MySQL Shell in
            Python mode. If you omit <code class="literal">--py</code>,
            MySQL Shell starts in JavaScript mode.
          </p></li><li class="listitem"><p>
            The default schema for this session is the
            <code class="literal">world_x</code> database. For instructions on
            setting up the <code class="literal">world_x</code> database sample,
            see <a class="xref" href="document-store.html#mysql-shell-tutorial-python-download" title="20.4.1 Import Database Sample">Section 20.4.1, “Import Database Sample”</a>.
</p></li></ul>
</div>
<p>
        Once MySQL Shell opens, the <code class="literal">mysql-py&gt;</code>
        prompt indicates that the active language for this session is
        Python.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt;
</pre><p>
        When you run <span class="command"><strong>mysqlsh</strong></span> without the host
        argument, MySQL Shell attempts to connect to the server
        instance running on the localhost interface on port 33060. For
        more information, see <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connections.html" target="_top">MySQL Shell Connections</a>.
      </p><p>
        MySQL Shell supports input-line editing as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <span class="keycap"><strong>left-arrow</strong></span> and <span class="keycap"><strong>right-arrow</strong></span>
            keys move horizontally within the current input line.
          </p></li><li class="listitem"><p>
            <span class="keycap"><strong>up-arrow</strong></span> and <span class="keycap"><strong>down-arrow</strong></span>
            keys move up and down through the set of previously entered
            lines.
          </p></li><li class="listitem"><p>
            <span class="keycap"><strong>Backspace</strong></span> deletes the character before the
            cursor and typing new characters enters them at the cursor
            position.
          </p></li><li class="listitem"><p>
            <span class="keycap"><strong>Enter</strong></span> sends the current input line to the
            server.
</p></li></ul>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="mysql-shell-tutorial-python-shell-help"></a>Get Help for MySQL Shell</h4>

</div>

</div>

</div>
<p>
        Type <span class="command"><strong>mysqlsh --help</strong></span> at the prompt of your
        command interpreter for a list of command-line options.
      </p><pre data-lang="terminal" class="programlisting">
<strong class="userinput"><code>mysqlsh --help</code></strong>
</pre><p>
        Type <code class="literal">\help</code> at the MySQL Shell prompt for a
        list of available commands and their descriptions.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>\help</code></strong>
</pre><p>
        Type <code class="literal">\help</code> followed by a command name for
        detailed help about an individual MySQL Shell command. For
        example, to view help on the <code class="literal">\connect</code>
        command, type:
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>\help \connect</code></strong>
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="mysql-shell-tutorial-python-shell-quit"></a>Quit MySQL Shell</h4>

</div>

</div>

</div>
<p>
        To quit MySQL Shell, type the following command:
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>\quit</code></strong>
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091612500352"></a>Related Information</h4>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            See <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-interactive-code-execution.html" target="_top">Interactive Code Execution</a>
            for an explanation of how interactive code execution works
            in MySQL Shell.
          </p></li><li class="listitem"><p>
            See <a class="ulink" href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-getting-started.html" target="_top">Getting Started with MySQL Shell</a> to learn
            about session and connection alternatives.
          </p></li><li class="listitem"><p>
            See
            <a class="xref" href="programs.html#connecting-using-uri-or-key-value-pairs" title="4.2.4 Connecting Using a URI or Key-Value Pairs">Section 4.2.4, “Connecting Using a URI or Key-Value Pairs”</a> to
            learn about specifying the location of a MySQL server.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-python-documents-collections"></a>20.4.3 Documents and Collections</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-collections-operations">20.4.3.1 Create, List, and Drop Collections</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-add">20.4.3.2 Add Documents</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-find">20.4.3.3 Find Documents</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-modify">20.4.3.4 Modify Documents</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-remove">20.4.3.5 Remove Documents</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-documents-index">20.4.3.6 Create and Drop Indexes</a></span></dt></dl>
</div>
<p>
      In MySQL, collections contain JSON documents that you can add,
      find, update, and remove. Collections are containers within a
      schema that you can create, list, and drop.
    </p><p>
      The examples in this section use the countryinfo collection in the
      <code class="literal">world_x</code> database. For instructions on setting
      up the <code class="literal">world_x</code> database sample, see
      <a class="xref" href="document-store.html#mysql-shell-tutorial-python-download" title="20.4.1 Import Database Sample">Section 20.4.1, “Import Database Sample”</a>.
</p>
<h4><a name="mysql-shell-tutorial-python-documents"></a>Documents</h4>
<p>
      In MySQL, documents are represented as JSON objects. Internally,
      they are stored in an efficient binary format that enables fast
      lookups and updates.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Simple document format for Python:
        </p><pre class="screen">
{"field1": "value", "field2" : 10, "field 3": null}
</pre></li></ul>
</div>
<p>
      An array of documents consists of a set of documents separated by
      commas and enclosed within <code class="literal">[</code> and
      <code class="literal">]</code> characters.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Simple array of documents for Python:
        </p><pre class="screen">
[{"Name": "Aruba", "_id": "ABW"}, {"Name": "Angola", "_id": "AGO"}]
</pre></li></ul>
</div>
<p>
      MySQL supports the following Python value types in JSON documents:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          numbers (integer and floating point)
        </p></li><li class="listitem"><p>
          strings
        </p></li><li class="listitem"><p>
          boolean (False and True)
        </p></li><li class="listitem"><p>
          None
        </p></li><li class="listitem"><p>
          arrays of more JSON values
        </p></li><li class="listitem"><p>
          nested (or embedded) objects of more JSON values
</p></li></ul>
</div>
<h4><a name="mysql-shell-tutorial-python-collections"></a>Collections</h4>
<p>
      Collections are containers for documents that share a purpose and
      possibly share one or more indexes. Each collection has a unique
      name and exists within a single schema.
    </p><p>
      The term schema is equivalent to a database, which means a group
      of database objects (as opposed to relational schema used to
      enforce structure and constraints over data). A schema does not
      enforce conformity on the documents in a collection.
    </p><p>
      In this quick-start guide:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Basic objects include:
</p>
<div class="informaltable">
<table summary="Objects to use interactively in MySQL Shell"><col width="40%"><col width="60%"><thead><tr>
              <th scope="col">Object form</th>
              <th scope="col">Description</th>
            </tr></thead><tbody><tr>
              <td scope="row"><code class="literal">db</code></td>
              <td><code class="literal">db</code> is a global variable assigned to the current
                active schema. When you want to run operations against
                the schema, for example to retrieve a collection, you
                use methods available for the <code class="literal">db</code>
                variable.</td>
            </tr><tr>
              <td scope="row"><code class="literal">db.get_collections()</code></td>
              <td><a class="link" href="document-store.html#mysql-shell-tutorial-python-collections-get" title="List Collections">db.get_collections()</a>
                holds a list of collections in the schema. Use the list
                to get references to collection objects, iterate over
                them, and so on.</td>
</tr></tbody></table>
</div>
</li><li class="listitem"><p>
          Basic operations scoped by collections include:
</p>
<div class="informaltable">
<table summary="CRUD operations available in X DevAPI"><col width="40%"><col width="60%"><thead><tr>
              <th scope="col">Operation form</th>
              <th scope="col">Description</th>
            </tr></thead><tbody><tr>
              <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.add()</code></td>
              <td>The
                <a class="link" href="document-store.html#mysql-shell-tutorial-python-documents-add" title="20.4.3.2 Add Documents">add()</a>
                method inserts one document or a list of documents into
                the named collection.</td>
            </tr><tr>
              <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.find()</code></td>
              <td>The
                <a class="link" href="document-store.html#mysql-shell-tutorial-python-documents-find" title="20.4.3.3 Find Documents">find()</a>
                method returns some or all documents in the named
                collection.</td>
            </tr><tr>
              <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.modify()</code></td>
              <td>The
                <a class="link" href="document-store.html#mysql-shell-tutorial-python-documents-modify" title="20.4.3.4 Modify Documents">modify()</a>
                method updates documents in the named collection.</td>
            </tr><tr>
              <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.remove()</code></td>
              <td>The
                <a class="link" href="document-store.html#mysql-shell-tutorial-python-documents-remove" title="20.4.3.5 Remove Documents">remove()</a>
                method deletes one document or a list of documents from
                the named collection.</td>
</tr></tbody></table>
</div>
</li></ul>
</div>
<h4><a name="idm140091612434528"></a>Related Information</h4>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-working-with-collections.html" target="_top">Working with Collections</a>
          for a general overview.
        </p></li><li class="listitem"><p>
          <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/mysql-x-crud-ebnf-definitions.html" target="_top">CRUD EBNF Definitions</a> provides a
          complete list of operations.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-collections-operations"></a>20.4.3.1 Create, List, and Drop Collections</h4>

</div>

</div>

</div>
<p>
        In MySQL Shell, you can create new collections, get a list of
        the existing collections in a schema, and remove an existing
        collection from a schema. Collection names are case-sensitive
        and each collection name must be unique.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612428304"></a>Confirm the Schema</h5>
</div>
</div>
</div>
<p>
          To show the value that is assigned to the schema variable,
          issue:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db</code></strong>
</pre><p>
          If the schema value is not <code class="literal">Schema:world_x</code>,
          then set the <code class="literal">db</code> variable by issuing:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>\use world_x</code></strong>
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-python-collections-create"></a>Create a Collection</h5>

</div>

</div>

</div>
<p>
          To create a new collection in an existing schema, use the
          <code class="literal">db</code> object's
          <code class="literal">createCollection()</code> method. The following
          example creates a collection called <code class="literal">flags</code>
          in the <code class="literal">world_x</code> database.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.create_collection("flags")</code></strong>
</pre><p>
          The method returns a collection object.
</p><pre class="screen">&lt;Collection:flags&gt;</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-python-collections-get"></a>List Collections</h5>

</div>

</div>

</div>
<p>
          To display all collections in the <code class="literal">world_x</code>
          database, use the <code class="literal">db</code> object's
          <code class="literal">get_collections()</code> method. Collections
          returned by the server you are currently connected to appear
          between brackets.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.get_collections()</code></strong>
<code class="computeroutput">[
    &lt;Collection:countryinfo&gt;,
    &lt;Collection:flags&gt;
]</code>
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-python-collections-drop"></a>Drop a Collection</h5>

</div>

</div>

</div>
<p>
          To drop an existing collection from a database, use the
          <code class="literal">db</code> object's
          <code class="literal">drop_collection()</code> method. For example, to
          drop the flags collection from the <code class="literal">world_x</code>
          database, type:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>session.drop_collection("world_x", "flags")</code></strong>
</pre><p>
          The <code class="literal">drop_collection()</code> method is also used
          in MySQL Shell to drop a relational table from a database.

</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612401072"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/collection-objects.html" target="_top">Collection Objects</a> for more
              examples.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-documents-add"></a>20.4.3.2 Add Documents</h4>

</div>

</div>

</div>
<p>
        Use the <code class="literal">add()</code> method to insert one document
        or a list of documents into an existing collection using
        MySQL Shell. All examples in this section use the countryinfo
        collection, which is provided in the <code class="literal">world_x</code>
        schema.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612394880"></a>Add a Document</h5>
</div>
</div>
</div>
<p>
          Insert the following document into the countryinfo collection.
          As this is multi-line content, press <span class="keycap"><strong>Enter</strong></span>
          twice to insert the document.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.add(
 {
    GNP: .6,
    IndepYear: 1967,
    Name: "Sealand",
    demographics: {
        LifeExpectancy: 79,
        Population: 27
    },
    geography: {
        Continent: "Europe",
        Region: "British Islands",
        SurfaceArea: 193
    },
    government: {
        GovernmentForm: "Monarchy",
        HeadOfState: "Michael Bates"
    }
  }
)</code></strong>
</pre><p>
          The method returns the status of the operation.
        </p><p>
          Each document requires an identifier field called
          <code class="literal">_id</code>. The value of the
          <code class="literal">_id</code> field must be unique among all
          documents in the same collection. In MySQL 8.0.11 and higher,
          document IDs are generated by the server, not the client, so
          MySQL Shell does not automatically set an
          <code class="literal">_id</code> value. A MySQL server at 8.0.11 or
          higher sets an <code class="literal">_id</code> value if the document
          does not contain the <code class="literal">_id</code> field. A MySQL
          server at an earlier 8.0 release or at 5.7 does not set an
          <code class="literal">_id</code> value in this situation, so you must
          specify it explicitly. If you do not, MySQL Shell returns
          error 5115 <span class="errortext">Document is missing a required
          field</span>.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612384816"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-crud-functions.html#crud-ebnf-collectionaddfunction" target="_top">CollectionAddFunction</a> for
              the full syntax definition.
            </p></li><li class="listitem"><p>
              See
              <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html" target="_top">Understanding Document IDs</a>.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-documents-find"></a>20.4.3.3 Find Documents</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">find()</code> method to query for
        and return documents from a collection in a database.
        MySQL Shell provides additional methods to use with the
        <code class="literal">find()</code> method to filter and sort the returned
        documents.
      </p><p>
        MySQL provides the following operators to specify search
        conditions: <code class="literal">OR</code> (<code class="literal">||</code>),
        <code class="literal">AND</code> (<code class="literal">&amp;&amp;</code>),
        <code class="literal">XOR</code>, <code class="literal">IS</code>,
        <code class="literal">NOT</code>, <code class="literal">BETWEEN</code>,
        <code class="literal">IN</code>, <code class="literal">LIKE</code>,
        <code class="literal">!=</code>, <code class="literal">&lt;&gt;</code>,
        <code class="literal">&gt;</code>, <code class="literal">&gt;=</code>,
        <code class="literal">&lt;</code>, <code class="literal">&lt;=</code>,
        <code class="literal">&amp;</code>, <code class="literal">|</code>,
        <code class="literal">&lt;&lt;</code>, <code class="literal">&gt;&gt;</code>,
        <code class="literal">+</code>, <code class="literal">-</code>,
        <code class="literal">*</code>, <code class="literal">/</code>,
        <code class="literal">~</code>, and <code class="literal">%</code>.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-python-documents-find-all"></a>Find All Documents in a Collection</h5>
</div>
</div>
</div>
<p>
          To return all documents in a collection, use the
          <code class="literal">find()</code> method without specifying search
          conditions. For example, the following operation returns all
          documents in the countryinfo collection.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find()</code></strong>
[
     {
          "GNP": 828,
          "IndepYear": null,
          "Name": "Aruba",
          "_id": "ABW",
          "demographics": {
              "LifeExpectancy": 78.4000015258789,
              "Population": 103000
          },
          "geography": {
              "Continent": "North America",
              "Region": "Caribbean",
              "SurfaceArea": 193
          },
          "government": {
              "GovernmentForm": "Nonmetropolitan Territory of The Netherlands",
              "HeadOfState": "Beatrix"
          }
          ...
      }
 ]
240 documents in set (0.00 sec)
</pre><p>
          The method produces results that contain operational
          information in addition to all documents in the collection.
        </p><p>
          An empty set (no matching documents) returns the following
          information:
        </p><pre class="screen">
Empty set (0.00 sec) 
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="mysql-shell-tutorial-python-documents-find-filter-searches"></a>Filter Searches</h5>

</div>

</div>

</div>
<p>
          You can include search conditions with the
          <code class="literal">find()</code> method. The syntax for expressions
          that form a search condition is the same as that of
          traditional MySQL <a class="xref" href="functions.html" title="Chapter 12 Functions and Operators">Chapter 12, <i>Functions and Operators</i></a>. You must
          enclose all expressions in quotes.
        </p><p>
          All examples in this section use the countryinfo collection in
          the <code class="literal">world_x</code> database. For the sake of
          brevity, some of the examples do not display output.
        </p><p>
          A simple search condition could consist of the
          <code class="literal">Name</code> field and a value we know is in a
          document. The following example returns a single document:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find("Name = 'Australia'")</code></strong>
[
    {
        "GNP": 351182,
        "IndepYear": 1901,
        "Name": "Australia",
        "_id": "AUS",
        "demographics": {
            "LifeExpectancy": 79.80000305175781,
            "Population": 18886000
        },
        "geography": {
            "Continent": "Oceania",
            "Region": "Australia and New Zealand",
            "SurfaceArea": 7741220
        },
        "government": {
            "GovernmentForm": "Constitutional Monarchy, Federation",
            "HeadOfState": "Elisabeth II"
        }
    }
]
</pre><p>
          The following example searches for all countries that have a
          GNP higher than $500 billion. The countryinfo collection
          measures GNP in units of million.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find("GNP &gt; 500000")</code></strong>
...[<span class="emphasis"><em>output removed</em></span>]
10 documents in set (0.00 sec)
</pre><p>
          The Population field in the following query is embedded within
          the demographics object. To access the embedded field, use a
          period between demographics and Population to identify the
          relationship. Document and field names are case-sensitive.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find("GNP &gt; 500000 and demographics.Population &lt; 100000000")</code></strong>
...[<span class="emphasis"><em>output removed</em></span>]
6 documents in set (0.00 sec)
</pre><p>
          Arithmetic operators in the following expression are used to
          query for countries with a GNP per capita higher than $30000.
          Search conditions can include arithmetic operators and most
          MySQL functions.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            Seven documents in the countryinfo collection have a
            population value of zero. Warning messages appear at the end
            of the output.
</p>
</div>
<pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find("GNP*1000000/demographics.Population &gt; 30000")</code></strong>
...[<span class="emphasis"><em>output removed</em></span>]
9 documents in set, 7 warnings (0.00 sec)
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
</pre><p>
          You can separate a value from the search condition by using
          the <code class="literal">bind()</code> method. For example, instead of
          specifying a hard-coded country name as the condition,
          substitute a named placeholder consisting of a colon followed
          by a name that begins with a letter, such as
          <span class="emphasis"><em>country</em></span>. Then use the
          <code class="literal">bind(<em class="replaceable"><code>placeholder</code></em>,
          <em class="replaceable"><code>value</code></em>)</code> method as follows:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find("Name = :country").bind("country", "Italy")</code></strong>
[
    {
        "GNP": 1161755,
        "IndepYear": 1861,
        "Name": "Italy",
        "_id": "ITA",
        "demographics": {
            "LifeExpectancy": 79,
            "Population": 57680000
        },
        "geography": {
            "Continent": "Europe",
            "Region": "Southern Europe",
            "SurfaceArea": 301316
        },
        "government": {
            "GovernmentForm": "Republic",
            "HeadOfState": "Carlo Azeglio Ciampi"
        }
    }
]
1 document in set (0.01 sec)
</pre>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Tip
</div>
<p>
            Within a program, binding enables you to specify
            placeholders in your expressions, which are filled in with
            values before execution and can benefit from automatic
            escaping, as appropriate.
          </p><p>
            Always use binding to sanitize input. Avoid introducing
            values in queries using string concatenation, which can
            produce invalid input and, in some cases, can cause security
            issues.
</p>
</div>
<p>
          You can use placeholders and the <code class="literal">bind()</code>
          method to create saved searches which you can then call with
          different values. For example to create a saved search for a
          country:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>myFind = db.countryinfo.find("Name = :country")</code></strong>
mysql-py&gt; <strong class="userinput"><code>myFind.bind('country', 'France')</code></strong>
[
    {
        "GNP": 1424285,
        "IndepYear": 843,
        "Name": "France",
        "_id": "FRA",
        "demographics": {
            "LifeExpectancy": 78.80000305175781,
            "Population": 59225700
        },
        "geography": {
            "Continent": "Europe",
            "Region": "Western Europe",
            "SurfaceArea": 551500
        },
        "government": {
            "GovernmentForm": "Republic",
            "HeadOfState": "Jacques Chirac"
        }
    }
]
1 document in set (0.0028 sec)

mysql-py&gt; <strong class="userinput"><code>myFind.bind('country', 'Germany')</code></strong>
[
    {
        "GNP": 2133367, 
        "IndepYear": 1955, 
        "Name": "Germany", 
        "_id": "DEU", 
        "demographics": {
            "LifeExpectancy": 77.4000015258789, 
            "Population": 82164700
        }, 
        "geography": {
            "Continent": "Europe", 
            "Region": "Western Europe", 
            "SurfaceArea": 357022
        }, 
        "government": {
            "GovernmentForm": "Federal Republic", 
            "HeadOfState": "Johannes Rau"
        }
    }
]
1 document in set (0.0026 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612320336"></a>Project Results</h5>

</div>

</div>

</div>
<p>
          You can return specific fields of a document, instead of
          returning all the fields. The following example returns the
          GNP and Name fields of all documents in the countryinfo
          collection matching the search conditions.
        </p><p>
          Use the <code class="literal">fields()</code> method to pass the list of
          fields to return.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find("GNP &gt; 5000000").fields(["GNP", "Name"])</code></strong>
[
    {
        "GNP": 8510700,
        "Name": "United States"
    }
]
1 document in set (0.00 sec)
 </pre><p>
          In addition, you can alter the returned
          documents—adding, renaming, nesting and even computing
          new field values—with an expression that describes the
          document to return. For example, alter the names of the fields
          with the following expression to return only two documents.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find().\
fields(mysqlx.expr('{"Name": upper(Name), "GNPPerCapita": GNP*1000000/demographics.Population}')).\
limit(2)</code></strong>
[
    {
        "GNPPerCapita": 8038.834951456311,
        "Name": "ARUBA"
    },
    {
        "GNPPerCapita": 263.0281690140845,
        "Name": "AFGHANISTAN"
    }
]
2 documents in set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612312704"></a>Limit, Sort, and Skip Results</h5>

</div>

</div>

</div>
<p>
          You can apply the <code class="literal">limit()</code>,
          <code class="literal">sort()</code>, and <code class="literal">skip()</code>
          methods to manage the number and order of documents returned
          by the <code class="literal">find()</code> method.
        </p><p>
          To specify the number of documents included in a result set,
          append the <code class="literal">limit()</code> method with a value to
          the <code class="literal">find()</code> method. The following query
          returns the first five documents in the countryinfo
          collection.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find().limit(5)</code></strong>
... [<span class="emphasis"><em>output removed</em></span>]
5 documents in set (0.00 sec)
</pre><p>
          To specify an order for the results, append the
          <code class="literal">sort()</code> method to the
          <code class="literal">find()</code> method. Pass to the
          <code class="literal">sort()</code> method a list of one or more fields
          to sort by and, optionally, the descending
          (<code class="literal">desc</code>) or ascending
          (<code class="literal">asc</code>) attribute as appropriate. Ascending
          order is the default order type.
        </p><p>
          For example, the following query sorts all documents by the
          IndepYear field and then returns the first eight documents in
          descending order.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find().sort(["IndepYear desc"]).limit(8)</code></strong>
... [<span class="emphasis"><em>output removed</em></span>]
8 documents in set (0.00 sec)
</pre><p>
          By default, the <code class="literal">limit()</code> method starts from
          the first document in the collection. You can use the
          <code class="literal">skip()</code> method to change the starting
          document. For example, to ignore the first document and return
          the next eight documents matching the condition, pass to the
          <code class="literal">skip()</code> method a value of 1.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find().sort(["IndepYear desc"]).limit(8).skip(1)</code></strong>
... [<span class="emphasis"><em>output removed</em></span>]
8 documents in set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612292224"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              The <a class="link" href="functions.html" title="Chapter 12 Functions and Operators">MySQL Reference
              Manual</a> provides detailed documentation on functions
              and operators.
            </p></li><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-crud-functions.html#crud-ebnf-collectionfindfunction" target="_top">CollectionFindFunction</a> for
              the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-documents-modify"></a>20.4.3.4 Modify Documents</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">modify()</code> method to update one
        or more documents in a collection. The X DevAPI provides
        additional methods for use with the <code class="literal">modify()</code>
        method to:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Set and unset fields within documents.
          </p></li><li class="listitem"><p>
            Append, insert, and delete arrays.
          </p></li><li class="listitem"><p>
            Bind, limit, and sort the documents to be modified.
</p></li></ul>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612281344"></a>Set and Unset Fields</h5>

</div>

</div>

</div>
<p>
          The <code class="literal">modify()</code> method works by filtering a
          collection to include only the documents to be modified and
          then applying the operations that you specify to those
          documents.
        </p><p>
          In the following example, the <code class="literal">modify()</code>
          method uses the search condition to identify the document to
          change and then the <code class="literal">set()</code> method replaces
          two values within the nested demographics object.

          
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.modify("_id = 'SEA'").\
set("demographics", {"LifeExpectancy": 78, "Population": 28})</code></strong>
Query OK, 1 item affected (0.04 sec)
</pre><p>
          After you modify a document, use the <code class="literal">find()</code>
          method to verify the change.
        </p><p>
          To remove content from a document, use the
          <code class="literal">modify()</code> and <code class="literal">unset()</code>
          methods. For example, the following query removes the GNP from
          a document that matches the search condition.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.modify("Name = 'Sealand'").unset("GNP")</code></strong>
Query OK, 1 item affected (0.01 sec)   
</pre><p>
          Use the <code class="literal">find()</code> method to verify the change.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.find("Name = 'Sealand'")</code></strong>
[
    {
        "IndepYear": 1967,
        "Name": "Sealand",
        "_id": "SEA",
        "demographics": {
            "LifeExpectancy": 78,
            "Population": 28
        },
        "geography": {
            "Continent": "Europe",
            "Region": "British Islands",
            "SurfaceArea": 193
        },
        "government": {
            "GovernmentForm": "Monarchy",
            "HeadOfState": "Michael Bates"
        }
    }
]
1 document in set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612266336"></a>Append, Insert, and Delete Arrays</h5>

</div>

</div>

</div>
<p>
          To append an element to an array field, or insert, or delete
          elements in an array, use the
          <code class="literal">array_append()</code>,
          <code class="literal">array_insert()</code>, or
          <code class="literal">array_delete()</code> methods. The following
          examples modify the countryinfo collection to enable tracking
          of international airports.
        </p><p>
          The first example uses the <code class="literal">modify()</code> and
          <code class="literal">set()</code> methods to create a new Airports
          field in all documents.
</p>
<div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Caution
</div>
<p>
            Use care when you modify documents without specifying a
            search condition. This action will modify all documents in
            the collection.
</p>
</div>
<pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.modify(True).set("Airports", [])</code></strong>
Query OK, 240 items affected (0.07 sec)
</pre><p>
          With the Airports field added, the next example uses the
          <code class="literal">array_append()</code> method to add a new airport
          to one of the documents. <span class="emphasis"><em>$.Airports</em></span> in
          the following example represents the Airports field of the
          current document.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.modify("Name = 'France'").array_append("$.Airports", "ORY")</code></strong>
Query OK, 1 item affected (0.02 sec)
</pre><p>
          Use <strong class="userinput"><code>db.countryinfo.find("Name =
          'France'")</code></strong> to see the change.
        </p><p>
          To insert an element at a different position in the array, use
          the <code class="literal">array_insert()</code> method to specify which
          index to insert in the path expression. In this case, the
          index is 0, or the first element in the array.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.modify("Name = 'France'").array_insert("$.Airports[0]", "CDG")</code></strong>
Query OK, 1 item affected (0.04 sec)
</pre><p>
          To delete an element from the array, you must pass to the
          <code class="literal">array_delete()</code> method the index of the
          element to be deleted.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.modify("Name = 'France'").array_delete("$.Airports[1]")</code></strong>
Query OK, 1 item affected (0.03 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612247216"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              The <a class="link" href="data-types.html#json-paths" title="Searching and Modifying JSON Values">MySQL Reference
              Manual</a> provides instructions to help you search for
              and modify JSON values.
            </p></li><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-crud-functions.html#crud-ebnf-collectionmodifyfunction" target="_top">CollectionModifyFunction</a>
              for the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-documents-remove"></a>20.4.3.5 Remove Documents</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">remove()</code> method to delete
        some or all documents from a collection in a database. The
        X DevAPI provides additional methods for use with the
        <code class="literal">remove()</code> method to filter and sort the
        documents to be removed.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612239184"></a>Remove Documents Using Conditions</h5>
</div>
</div>
</div>
<p>
          The example that follows passes a search condition to the
          <code class="literal">remove()</code> method. All documents matching the
          condition will be removed from the countryinfo collection. In
          this example, one document matches the condition.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.remove("_id = 'SEA'")</code></strong>
Query OK, 1 item affected (0.02 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612235296"></a>Remove the First Document</h5>

</div>

</div>

</div>
<p>
          To remove the first document in the countryinfo collection,
          use the <code class="literal">limit()</code> method with a value of 1.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.remove(True).limit(1)</code></strong>
Query OK, 1 item affected (0.03 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612231536"></a>Remove the Last Document in an Order</h5>

</div>

</div>

</div>
<p>
          The following example removes the last document in the
          countryinfo collection by country name.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.countryinfo.remove(True).sort(["Name desc"]).limit(1)</code></strong>
Query OK, 1 item affected (0.02 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612228448"></a>Remove All Documents in a Collection</h5>

</div>

</div>

</div>
<p>
          You can remove all documents in a collection. To do so, use
          the <code class="literal">remove(True)</code> method without specifying
          a search condition.
</p>
<div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Caution
</div>
<p>
            Use care when you remove documents without specifying a
            search condition. This action will delete all documents from
            the collection.
</p>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612225424"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-crud-functions.html#crud-ebnf-collectionremovefunction" target="_top">CollectionRemoveFunction</a>
              for the full syntax definition.
            </p></li><li class="listitem"><p>
              See <a class="xref" href="document-store.html#mysql-shell-tutorial-python-download" title="20.4.1 Import Database Sample">Section 20.4.1, “Import Database Sample”</a>
              for instructions to recreate the
              <code class="literal">world_x</code> database.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-documents-index"></a>20.4.3.6 Create and Drop Indexes</h4>

</div>

</div>

</div>
<p>
        Indexes are used to find documents with specific field values
        quickly. Without an index, MySQL must begin with the first
        document and then read through the entire collection to find the
        relevant fields. The larger the collection, the more this costs.
        If a collection is large and queries on a specific field are
        common, then consider creating an index on a specific field
        inside a document.
      </p><p>
        For example, the following query will perform better with an
        index:
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.find("demographics.Population &lt; 100")</code></strong>
...[<span class="emphasis"><em>output removed</em></span>]
8 documents in set (0.00 sec)
</pre><p>
        The <code class="literal">create_index()</code> method creates an index
        that you can define as nonunique or unique. Use the
        <code class="literal">field()</code> method to chain the fields that
        should be indexed. The <code class="literal">execute()</code> method is
        required to create or drop an index.
      </p><p>
        In MySQL, the <code class="literal">_id</code> field is equivalent to a
        primary key by default.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612211456"></a>Add a Nonunique Index</h5>
</div>
</div>
</div>
<p>
          To create a nonunique index, pass to the
          <code class="literal">create_index()</code> method an index name.
          Duplicate index names are prohibited.
        </p><p>
          In the following example, the first parameter of the
          <code class="literal">field()</code> method specifies the Population
          field inside the demographics object and the next parameter
          indicates that the field should be indexed as an Integer
          numeric value. The last parameter indicates whether the field
          should require the NOT NULL constraint. If the value is
          <code class="literal">False</code>, the field can contain
          <code class="literal">NULL</code> values.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.create_index("pop").\
field("demographics.Population", "INTEGER", False).execute()</code></strong>
Query OK (0.04 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612204688"></a>Add a Unique Index</h5>

</div>

</div>

</div>
<p>
          To create a unique index, pass to the
          <code class="literal">create_index()</code> method an index name and the
          <code class="literal">mysqlx.IndexType.UNIQUE</code> type. Country
          <code class="literal">"Name"</code> is another common field in the
          countryinfo collection to index. In the following example,
          <code class="literal">"Text(40)"</code> represents the number of
          characters to index and <code class="literal">True</code> indicates that
          the field cannot contain any <code class="literal">NULL</code> values.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.create_index("name", mysqlx.IndexType.UNIQUE).\
field("Name", "TEXT(40)", True).execute()</code></strong>
Query OK (0.04 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612197168"></a>Drop an Index</h5>

</div>

</div>

</div>
<p>
          To drop an index, pass to the <code class="literal">drop_index()</code>
          method the name of the index to drop. For example, you can
          drop the <span class="quote">“<span class="quote">pop</span>”</span> index as follows:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-js&gt; <strong class="userinput"><code>db.countryinfo.drop_index("pop").execute()</code></strong>
Query OK (0.58 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612192976"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See
              <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-collection-index-management-functions.html" target="_top">Collection Index Management Functions</a>
              for the full syntax definition.

              
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-python-relational-tables"></a>20.4.4 Relational Tables</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-table-insert">20.4.4.1 Insert Records into Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-table-select">20.4.4.2 Select Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-table-update">20.4.4.3 Update Tables</a></span></dt><dt><span class="section"><a href="document-store.html#mysql-shell-tutorial-python-table-delete">20.4.4.4 Delete Tables</a></span></dt></dl>
</div>
<p>
      You can use MySQL Shell to manipulate not just JSON documents,
      but also relational tables.
    </p><p>
      In MySQL, each relational table is associated with a particular
      storage engine. The examples in this section use
      <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> tables in the
      <code class="literal">world_x</code> database.
</p>
<h4><a name="idm140091612184832"></a>Confirm the Schema</h4>
<p>
      To show the value that is assigned to the schema variable, type
      <code class="literal">db</code>.
    </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db</code></strong>
&lt;Schema:world_x&gt;
</pre><p>
      If the schema value is not the <code class="literal">Schema:world_x</code>
      database, then set the <code class="literal">db</code> variable as follows:
    </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>\use world_x</code></strong>
Schema `world_x` accessible through db.
</pre>
<h4><a name="idm140091612177792"></a>Show All Tables</h4>
<p>
      To display all relational tables in the <code class="literal">world_x</code>
      database, use the <code class="literal">get_tables()</code> method on the
      schema object.
    </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.get_tables()</code></strong>
{
    "city": &lt;Table:city&gt;,
    "country": &lt;Table:country&gt;,
    "countrylanguage": &lt;Table:countrylanguage&gt;
}
</pre>
<h4><a name="idm140091612173264"></a>Basic Table Operations</h4>
<p>
      Basic operations scoped by tables include:
</p>
<div class="informaltable">
<table summary="CRUD operations to use interactively on tables within MySQL Shell"><col width="32%"><col width="68%"><thead><tr>
          <th scope="col">Operation form</th>
          <th scope="col">Description</th>
        </tr></thead><tbody><tr>
          <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.insert()</code></td>
          <td>The
            <a class="link" href="document-store.html#mysql-shell-tutorial-python-table-insert" title="20.4.4.1 Insert Records into Tables">insert()</a>
            method inserts one or more records into the named table.</td>
        </tr><tr>
          <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.select()</code></td>
          <td>The
            <a class="link" href="document-store.html#mysql-shell-tutorial-python-table-select" title="20.4.4.2 Select Tables">select()</a>
            method returns some or all records in the named table.</td>
        </tr><tr>
          <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.update()</code></td>
          <td>The
            <a class="link" href="document-store.html#mysql-shell-tutorial-python-table-update" title="20.4.4.3 Update Tables">update()</a>
            method updates records in the named table.</td>
        </tr><tr>
          <td scope="row"><code class="literal">db.<em class="replaceable"><code>name</code></em>.delete()</code></td>
          <td>The
            <a class="link" href="document-store.html#mysql-shell-tutorial-python-table-delete" title="20.4.4.4 Delete Tables">delete()</a>
            method deletes one or more records from the named table.</td>
</tr></tbody></table>
</div>
<h4><a name="idm140091612149072"></a>Related Information</h4>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          See
          <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-working-with-relational-tables.html" target="_top">Working with Relational Tables</a>
          for a general overview.
        </p></li><li class="listitem"><p>
          <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/mysql-x-crud-ebnf-definitions.html" target="_top">CRUD EBNF Definitions</a> provides a
          complete list of operations.
        </p></li><li class="listitem"><p>
          See <a class="xref" href="document-store.html#mysql-shell-tutorial-python-download" title="20.4.1 Import Database Sample">Section 20.4.1, “Import Database Sample”</a> for
          instructions on setting up the <code class="literal">world_x</code>
          database sample.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-table-insert"></a>20.4.4.1 Insert Records into Tables</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">insert()</code> method with the
        <code class="literal">values()</code> method to insert records into an
        existing relational table. The <code class="literal">insert()</code>
        method accepts individual columns or all columns in the table.
        Use one or more <code class="literal">values()</code> methods to specify
        the values to be inserted.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612137760"></a>Insert a Complete Record</h5>
</div>
</div>
</div>
<p>
          To insert a complete record, pass to the
          <code class="literal">insert()</code> method all columns in the table.
          Then pass to the <code class="literal">values()</code> method one value
          for each column. For example, to add a new record to the city
          table in the <code class="literal">world_x</code> database, insert the
          following record and press <span class="keycap"><strong>Enter</strong></span> twice.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.insert("ID", "Name", "CountryCode", "District", "Info").\
values(None, "Olympia", "USA", "Washington", '{"Population": 5000}')</code></strong>
Query OK, 1 item affected (0.01 sec)
</pre><p>
          The city table has five columns: ID, Name, CountryCode,
          District, and Info. Each value must match the data type of the
          column it represents.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612131024"></a>Insert a Partial Record</h5>

</div>

</div>

</div>
<p>
          The following example inserts values into the ID, Name, and
          CountryCode columns of the city table.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.insert("ID", "Name", "CountryCode").\
values(None, "Little Falls", "USA").values(None, "Happy Valley", "USA")</code></strong>
Query OK, 2 item affected (0.03 sec)
</pre><p>
          When you specify columns using the <code class="literal">insert()</code>
          method, the number of values must match the number of columns.
          In the previous example, you must supply three values to match
          the three columns specified.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612126624"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html#crud-ebnf-tableinsertfunction" target="_top">TableInsertFunction</a> for
              the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-table-select"></a>20.4.4.2 Select Tables</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">select()</code> method to query for
        and return records from a table in a database. The
        X DevAPI provides additional methods to use with the
        <code class="literal">select()</code> method to filter and sort the
        returned records.
      </p><p>
        MySQL provides the following operators to specify search
        conditions: <code class="literal">OR</code> (<code class="literal">||</code>),
        <code class="literal">AND</code> (<code class="literal">&amp;&amp;</code>),
        <code class="literal">XOR</code>, <code class="literal">IS</code>,
        <code class="literal">NOT</code>, <code class="literal">BETWEEN</code>,
        <code class="literal">IN</code>, <code class="literal">LIKE</code>,
        <code class="literal">!=</code>, <code class="literal">&lt;&gt;</code>,
        <code class="literal">&gt;</code>, <code class="literal">&gt;=</code>,
        <code class="literal">&lt;</code>, <code class="literal">&lt;=</code>,
        <code class="literal">&amp;</code>, <code class="literal">|</code>,
        <code class="literal">&lt;&lt;</code>, <code class="literal">&gt;&gt;</code>,
        <code class="literal">+</code>, <code class="literal">-</code>,
        <code class="literal">*</code>, <code class="literal">/</code>,
        <code class="literal">~</code>, and <code class="literal">%</code>.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612102032"></a>Select All Records</h5>
</div>
</div>
</div>
<p>
          To issue a query that returns all records from an existing
          table, use the <code class="literal">select()</code> method without
          specifying search conditions. The following example selects
          all records from the city table in the
          <code class="literal">world_x</code> database.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            Limit the use of the empty <code class="literal">select()</code>
            method to interactive statements. Always use explicit
            column-name selections in your application code.
</p>
</div>
<pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.select()</code></strong>
+------+------------+-------------+------------+-------------------------+
| ID   | Name       | CountryCode | District   | Info                    |
+------+------------+-------------+------------+-------------------------+
|    1 | Kabul      | AFG         | Kabol      |{"Population": 1780000}  |
|    2 | Qandahar   | AFG         | Qandahar   |{"Population": 237500}   |
|    3 | Herat      | AFG         | Herat      |{"Population": 186800}   |
...    ...          ...           ...          ...
| 4079 | Rafah      | PSE         | Rafah      |{"Population": 92020}    |
+------+------- ----+-------------+------------+-------------------------+
4082 rows in set (0.01 sec)
</pre><p>
          An empty set (no matching records) returns the following
          information:
        </p><pre class="screen">
Empty set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612094016"></a>Filter Searches</h5>

</div>

</div>

</div>
<p>
          To issue a query that returns a set of table columns, use the
          <code class="literal">select()</code> method and specify the columns to
          return between square brackets. This query returns the Name
          and CountryCode columns from the city table.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"])</code></strong>
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Kabul             | AFG         |
| Qandahar          | AFG         |
| Herat             | AFG         |
| Mazar-e-Sharif    | AFG         |
| Amsterdam         | NLD         |
...                 ...
| Rafah             | PSE         |
| Olympia           | USA         |
| Little Falls      | USA         |
| Happy Valley      | USA         |
+-------------------+-------------+
4082 rows in set (0.00 sec)
</pre><p>
          To issue a query that returns rows matching specific search
          conditions, use the <code class="literal">where()</code> method to
          include those conditions. For example, the following example
          returns the names and country codes of the cities that start
          with the letter Z.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"]).where("Name like 'Z%'")</code></strong>
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zaanstad          | NLD         |
| Zoetermeer        | NLD         |
| Zwolle            | NLD         |
| Zenica            | BIH         |
| Zagazig           | EGY         |
| Zaragoza          | ESP         |
| Zamboanga         | PHL         |
| Zahedan           | IRN         |
| Zanjan            | IRN         |
| Zabol             | IRN         |
| Zama              | JPN         |
| Zhezqazghan       | KAZ         |
| Zhengzhou         | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
59 rows in set (0.00 sec)
</pre><p>
          You can separate a value from the search condition by using
          the <code class="literal">bind()</code> method. For example, instead of
          using "Name = 'Z%' " as the condition, substitute a named
          placeholder consisting of a colon followed by a name that
          begins with a letter, such as <span class="emphasis"><em>name</em></span>. Then
          include the placeholder and value in the
          <code class="literal">bind()</code> method as follows:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"]).\
              where("Name like :name").bind("name", "Z%")</code></strong>
</pre>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Tip
</div>
<p>
            Within a program, binding enables you to specify
            placeholders in your expressions, which are filled in with
            values before execution and can benefit from automatic
            escaping, as appropriate.
          </p><p>
            Always use binding to sanitize input. Avoid introducing
            values in queries using string concatenation, which can
            produce invalid input and, in some cases, can cause security
            issues.
</p>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612079808"></a>Project Results</h5>

</div>

</div>

</div>
<p>
          To issue a query using the <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a>
          operator, add the operator between search conditions in the
          <code class="literal">where()</code> method.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"]).\
               where("Name like 'Z%' and CountryCode = 'CHN'")</code></strong>
+----------------+-------------+
| Name           | CountryCode |
+----------------+-------------+
| Zhengzhou      | CHN         |
| Zibo           | CHN         |
| Zhangjiakou    | CHN         |
| Zhuzhou        | CHN         |
| Zhangjiang     | CHN         |
| Zigong         | CHN         |
| Zaozhuang      | CHN         |
...              ...
| Zhangjiagang   | CHN         |
+----------------+-------------+
22 rows in set (0.01 sec)
</pre><p>
          To specify multiple conditional operators, you can enclose the
          search conditions in parenthesis to change the operator
          precedence. The following example demonstrates the placement
          of <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a> and
          <a class="link" href="functions.html#operator_or"><code class="literal">OR</code></a> operators.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.select(["Name", "CountryCode"]).\
where("Name like 'Z%' and (CountryCode = 'CHN' or CountryCode = 'RUS')")</code></strong>
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zhengzhou         | CHN         |
| Zibo              | CHN         |
| Zhangjiakou       | CHN         |
| Zhuzhou           | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
29 rows in set (0.01 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612069232"></a>Limit, Order, and Offset Results</h5>

</div>

</div>

</div>
<p>
          You can apply the <code class="literal">limit()</code>,
          <code class="literal">order_by()</code>, and <code class="literal">offset()</code>
          methods to manage the number and order of records returned by
          the <code class="literal">select()</code> method.
        </p><p>
          To specify the number of records included in a result set,
          append the <code class="literal">limit()</code> method with a value to
          the <code class="literal">select()</code> method. For example, the
          following query returns the first five records in the country
          table.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.country.select(["Code", "Name"]).limit(5)</code></strong>
+------+-------------+
| Code | Name        |
+------+-------------+
| ABW  | Aruba       |
| AFG  | Afghanistan |
| AGO  | Angola      |
| AIA  | Anguilla    |
| ALB  | Albania     |
+------+-------------+
5 rows in set (0.00 sec)
</pre><p>
          To specify an order for the results, append the
          <code class="literal">order_by()</code> method to the
          <code class="literal">select()</code> method. Pass to the
          <code class="literal">order_by()</code> method a list of one or more
          columns to sort by and, optionally, the descending
          (<code class="literal">desc</code>) or ascending
          (<code class="literal">asc</code>) attribute as appropriate. Ascending
          order is the default order type.
        </p><p>
          For example, the following query sorts all records by the Name
          column and then returns the first three records in descending
          order .
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.country.select(["Code", "Name"]).order_by(["Name desc"]).limit(3)</code></strong>
+------+------------+
| Code | Name       |
+------+------------+
| ZWE  | Zimbabwe   |
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
+------+------------+
3 rows in set (0.00 sec)
</pre><p>
          By default, the <code class="literal">limit()</code> method starts from
          the first record in the table. You can use the
          <code class="literal">offset()</code> method to change the starting
          record. For example, to ignore the first record and return the
          next three records matching the condition, pass to the
          <code class="literal">offset()</code> method a value of 1.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.country.select(["Code", "Name"]).order_by(["Name desc"]).limit(3).offset(1)</code></strong>
+------+------------+
| Code | Name       |
+------+------------+
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
| YEM  | Yemen      |
+------+------------+
3 rows in set (0.00 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612049600"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              The <a class="link" href="functions.html" title="Chapter 12 Functions and Operators">MySQL Reference
              Manual</a> provides detailed documentation on functions
              and operators.
            </p></li><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html#crud-ebnf-tableselectfunction" target="_top">TableSelectFunction</a> for
              the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-table-update"></a>20.4.4.3 Update Tables</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">update()</code> method to modify one
        or more records in a table. The <code class="literal">update()</code>
        method works by filtering a query to include only the records to
        be updated and then applying the operations you specify to those
        records.
      </p><p>
        To replace a city name in the city table, pass to the
        <code class="literal">set()</code> method the new city name. Then, pass to
        the <code class="literal">where()</code> method the city name to locate
        and replace. The following example replaces the city Peking with
        Beijing.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.update().set("Name", "Beijing").where("Name = 'Peking'")</code></strong>
Query OK, 1 item affected (0.04 sec)
</pre><p>
        Use the <code class="literal">select()</code> method to verify the change.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).where("Name = 'Beijing'")</code></strong>
+------+-----------+-------------+----------+-----------------------------+
| ID   | Name      | CountryCode | District | Info                        |
+------+-----------+-------------+----------+-----------------------------+
| 1891 | Beijing   | CHN         | Peking   | {"Population": 7472000}     |
+------+-----------+-------------+----------+-----------------------------+
1 row in set (0.00 sec)
</pre>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612034576"></a>Related Information</h5>
</div>
</div>
</div>

<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html#crud-ebnf-tableupdatefunction" target="_top">TableUpdateFunction</a> for
              the full syntax definition.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysql-shell-tutorial-python-table-delete"></a>20.4.4.4 Delete Tables</h4>

</div>

</div>

</div>
<p>
        You can use the <code class="literal">delete()</code> method to remove
        some or all records from a table in a database. The
        X DevAPI provides additional methods to use with the
        <code class="literal">delete()</code> method to filter and order the
        records to be deleted.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h5 class="title"><a name="idm140091612028320"></a>Delete Records Using Conditions</h5>
</div>
</div>
</div>
<p>
          The example that follows passes search conditions to the
          <code class="literal">delete()</code> method. All records matching the
          condition will be deleted from the city table. In this
          example, one record matches the condition.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.delete().where("Name = 'Olympia'")</code></strong>
Query OK, 1 item affected (0.01 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612024496"></a>Delete the First Record</h5>

</div>

</div>

</div>
<p>
          To delete the first record in the city table, use the
          <code class="literal">limit()</code> method with a value of 1.
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.delete().limit(1)</code></strong>
Query OK, 1 item affected (0.02 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612020768"></a>Delete All Records in a Table</h5>

</div>

</div>

</div>
<p>
          You can delete all records in a table. To do so, use the
          <code class="literal">delete()</code> method without specifying a search
          condition.
</p>
<div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Caution
</div>
<p>
            Use care when you delete records without specifying a search
            condition. This action will delete all records from the
            table.
</p>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612017744"></a>Drop a Table</h5>

</div>

</div>

</div>
<p>
          The <code class="literal">drop_collection()</code> method is also used
          in MySQL Shell to drop a relational table from a database.
          For example, to drop the citytest table from the
          <code class="literal">world_x</code> database, type:
        </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>session.drop_collection("world_x", "citytest")</code></strong>
Query OK (0.04 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h5 class="title"><a name="idm140091612013216"></a>Related Information</h5>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              See <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html#crud-ebnf-tabledeletefunction" target="_top">TableDeleteFunction</a> for
              the full syntax definition.
            </p></li><li class="listitem"><p>
              See <a class="xref" href="document-store.html#mysql-shell-tutorial-python-download" title="20.4.1 Import Database Sample">Section 20.4.1, “Import Database Sample”</a>
              for instructions to recreate the
              <code class="literal">world_x</code> database.
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysql-shell-tutorial-python-documents-in-tables"></a>20.4.5 Documents in Tables</h3>

</div>

</div>

</div>
<p>
      In MySQL, a table may contain traditional relational data, JSON
      values, or both. You can combine traditional data with JSON
      documents by storing the documents in columns having a native
      <a class="link" href="data-types.html#json" title="11.6 The JSON Data Type"><code class="literal">JSON</code></a> data type.
    </p><p>
      Examples in this section use the city table in the
      <code class="literal">world_x</code> database.
</p>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h4 class="title"><a name="idm140091612003536"></a>city Table Description</h4>
</div>
</div>
</div>
<p>
        The city table has five columns (or fields).
      </p><pre class="screen">
+---------------+------------+-------+-------+---------+------------------+
| Field         | Type       | Null  | Key   | Default | Extra            |
+---------------+------------+-------+-------+---------+------------------+
| ID            | int(11)    | NO    | PRI   | null    | auto_increment   |
| Name          | char(35)   | NO    |       |         |                  |
| CountryCode   | char(3)    | NO    |       |         |                  |
| District      | char(20)   | NO    |       |         |                  |
| Info          | json       | YES   |       | null    |                  |
+---------------+------------+-------+-------+---------+------------------+
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091612000912"></a>Insert a Record</h4>

</div>

</div>

</div>
<p>
        To insert a document into the column of a table, pass to the
        <code class="literal">values()</code> method a well-formed JSON document
        in the correct order. In the following example, a document is
        passed as the final value to be inserted into the Info column.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.insert().\
values(None, "San Francisco", "USA", "California", '{"Population":830000}')</code></strong>
Query OK, 1 item affected (0.01 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091611996976"></a>Select a Record</h4>

</div>

</div>

</div>
<p>
        You can issue a query with a search condition that evaluates
        document values in the expression.
      </p><pre data-lang="mysqlsh" class="programlisting">
mysql-py&gt; <strong class="userinput"><code>db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).\
  where("CountryCode = :country and Info-&gt;'$.Population' &gt; 1000000").\
  bind('country', 'USA')</code></strong>
+------+----------------+-------------+----------------+-----------------------------+
| ID   | Name           | CountryCode | District       | Info                        |
+------+----------------+-------------+----------------+-----------------------------+
| 3793 | New York       | USA         | New York       | {"Population": 8008278}     |
| 3794 | Los Angeles    | USA         | California     | {"Population": 3694820}     |
| 3795 | Chicago        | USA         | Illinois       | {"Population": 2896016}     |
| 3796 | Houston        | USA         | Texas          | {"Population": 1953631}     |
| 3797 | Philadelphia   | USA         | Pennsylvania   | {"Population": 1517550}     |
| 3798 | Phoenix        | USA         | Arizona        | {"Population": 1321045}     |
| 3799 | San Diego      | USA         | California     | {"Population": 1223400}     |
| 3800 | Dallas         | USA         | Texas          | {"Population": 1188580}     |
| 3801 | San Antonio    | USA         | Texas          | {"Population": 1144646}     |
+------+----------------+-------------+----------------+-----------------------------+
9 rows in set (0.01 sec)
</pre>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="idm140091611992704"></a>Related Information</h4>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            See
            <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-working-with-relational-tables-and-documents.html" target="_top">Working with Relational Tables and Documents</a>
            for a general overview.
          </p></li><li class="listitem"><p>
            See <a class="xref" href="data-types.html#json" title="11.6 The JSON Data Type">Section 11.6, “The JSON Data Type”</a> for a detailed description of the
            data type.
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="x-plugin"></a>20.5 X Plugin</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#x-plugin-checking-installation">20.5.1 Checking X Plugin Installation</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-disabling">20.5.2 Disabling X Plugin</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-ssl-connections">20.5.3 Using Secure Connections with X Plugin</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-sha2-cache-plugin">20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-options-variables">20.5.5 X Plugin Options and Variables</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-system-monitoring">20.5.6 Monitoring X Plugin</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140091611986944"></a><p>
    This section explains how to use, configure and monitor
    X Plugin.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="x-plugin-checking-installation"></a>20.5.1 Checking X Plugin Installation</h3>
</div>
</div>
</div>
<p>
      X Plugin is enabled by default in MySQL 8, therefore
      installing or upgrading to MySQL 8 makes the plugin available. You
      can verify X Plugin is installed on an instance of MySQL
      server by using the <a class="link" href="sql-syntax.html#show-plugins" title="13.7.6.25 SHOW PLUGINS Syntax"><code class="literal">SHOW plugins</code></a>
      statement to view the plugins list.
    </p><p>
      To use MySQL Shell to verify X Plugin is installed, issue:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; mysqlsh -u <em class="replaceable"><code>user</code></em> --sqlc -P 3306 -e "SHOW plugins"
</pre><p>
      To use MySQL Client to verify X Plugin is installed, issue:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; mysql -u <em class="replaceable"><code>user</code></em> -p -e "SHOW plugins"
</pre><p>
      An example result if X Plugin is installed is highlighted
      here:
    </p><pre data-lang="none" class="programlisting">
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+

...


| mysqlx                     | ACTIVE   | DAEMON             | NULL    | GPL     |

...

+----------------------------+----------+--------------------+---------+---------+
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="x-plugin-disabling"></a>20.5.2 Disabling X Plugin</h3>

</div>

</div>

</div>
<p>
      The X Plugin can be disabled at startup by either setting
      <a class="link" href="document-store.html#option_mysqld_mysqlx"><code class="option">mysqlx=0</code></a> in your MySQL
      configuration file, or by passing in either
      <a class="link" href="document-store.html#option_mysqld_mysqlx"><code class="option">--mysqlx=0</code></a> or
      <a class="link" href="document-store.html#option_mysqld_mysqlx"><code class="option">--skip-mysqlx</code></a>
      when starting the MySQL server.
    </p><p>
      Alternatively, use the
      <a class="link" href="installing.html#option_cmake_with_mysqlx"><code class="option">-DWITH_MYSQLX=OFF</code></a> CMake option to
      compile MySQL Server without X Plugin.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="x-plugin-ssl-connections"></a>20.5.3 Using Secure Connections with X Plugin</h3>

</div>

</div>

</div>
<p>
      This section explains how to configure X Plugin to use
      secure connections. For more background information, see
      <a class="xref" href="security.html#encrypted-connections" title="6.3 Using Encrypted Connections">Section 6.3, “Using Encrypted Connections”</a>.
    </p><p>
      X Plugin has its own SSL settings which can differ from
      those used with MySQL Server. This means that X Plugin can
      be configured with a different SSL key, certificate, and
      certificate authorities file than MySQL Server. Similarly,
      X Plugin has its own SSL status variables calculated
      independently from the MySQL Server SSL related variables. By
      default the X Plugin SSL configuration is taken from the
      <code class="literal">mysqlx_ssl_*</code> variables, described at
      <a class="xref" href="document-store.html#x-plugin-options-system-variables" title="20.5.5.2 X Plugin Options and System Variables">Section 20.5.5.2, “X Plugin Options and System Variables”</a>. If no
      configuration is provided using the
      <code class="literal">mysqlx_ssl_*</code> variables, X Plugin falls
      back to using the MySQL Server SSL system variables. This means
      you can choose to either have separate SSL configurations for
      MySQL Protocol and X Protocol connections by configuring
      each separately, or share the SSL configuration between MySQL
      Protocol and X Protocol connections by only configuring the
      <code class="literal">ssl-*</code> variables.
    </p><p>
      On a server with X Plugin installed, to configure MySQL
      Protocol and X Protocol connections with separate SSL
      configurations use both the <code class="literal">ssl-*</code> and
      <code class="literal">mysqlx-ssl-*</code> variables in
      <code class="filename">my.cnf</code>:
    </p><pre data-lang="ini" class="programlisting">
[mysqld]
ssl-ca=ca1.pem
ssl-cert=server-cert1.pem
ssl-key=server-key1.pem

mysqlx-ssl-ca=ca2.pem
mysqlx-ssl-cert=server-cert2.pem
mysqlx-ssl-key=server-key2.pem
</pre><p>
      The available <code class="literal">mysqlx_ssl_*</code> variables mirror the
      SSL variables in MySQL Server, so the files and techniques
      described for configuring MySQL Server to use SSL at
      <a class="xref" href="security.html#using-encrypted-connections" title="6.3.1 Configuring MySQL to Use Encrypted Connections">Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”</a> are relevant to
      configuring X Plugin to use secure connections.
    </p><p>
      You can configure the TLS versions used by X Protocol SSL
      connections using the <a class="link" href="server-administration.html#sysvar_tls_version"><code class="literal">tls_version</code></a>
      system variable. The TLS version used by MySQL Protocol and
      X Protocol connections is therefore the same TLS version.
    </p><p>
      Encryption per connection is optional, but a specific user can be
      forced to use encryption for X Protocol and MySQL Protocol
      connections. You configure such a user by issuing a
      <a class="link" href="sql-syntax.html#grant" title="13.7.1.6 GRANT Syntax"><code class="literal">GRANT</code></a> statement with the
      <code class="literal">REQUIRE</code> option. For more details see
      <a class="xref" href="sql-syntax.html#grant" title="13.7.1.6 GRANT Syntax">Section 13.7.1.6, “GRANT Syntax”</a>. Alternatively all X Protocol and
      MySQL Protocol connections can be forced to use encryption by
      setting <a class="link" href="server-administration.html#sysvar_require_secure_transport"><code class="literal">require_secure_transport</code></a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="x-plugin-sha2-cache-plugin"></a>20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin</h3>

</div>

</div>

</div>
<p>
      X Plugin supports MySQL accounts created with the
      <a class="xref" href="security.html#caching-sha2-pluggable-authentication" title="6.4.1.3 Caching SHA-2 Pluggable Authentication">Section 6.4.1.3, “Caching SHA-2 Pluggable Authentication”</a> plugin.
      You can use X Plugin to authenticate against such accounts
      using non-SSL connections with <code class="literal">SHA256_MEMORY</code>
      authentication and SSL connections with <code class="literal">PLAIN</code>
      authentication.
    </p><p>
      Although the caching SHA-2 authentication plugin holds an
      authentication cache, to use such accounts with X Plugin the
      X Plugin authentication cache plugin named
      <code class="literal">mysqlx_cache_cleaner</code> is used. Like
      X Plugin, it is enabled by default.
    </p><p>
      Before you can use non-SSL connections to authenticate an account
      which uses the <code class="literal">caching_sha2_password</code> plugin,
      the account must have authenticated at least once over an SSL
      connection to store the password in the X Plugin
      authentication cache. This means that the first use of an account
      must be done using an SSL connection with the X Plugin
      authentication cache enabled. Once this initial authentication
      over SSL has succeeded non-SSL connections can be used.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="x-plugin-options-variables"></a>20.5.5 X Plugin Options and Variables</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#x-plugin-option-variable-reference">20.5.5.1 X Plugin Option and Variable Reference</a></span></dt><dt><span class="section"><a href="document-store.html#x-plugin-options-system-variables">20.5.5.2 X Plugin Options and System Variables</a></span></dt></dl>
</div>
<p>
      This section describes the command options and system variables
      that configure X Plugin. If values specified at startup time
      are incorrect, X Plugin could fail to initialize properly
      and the server does not load it. In this case, the server could
      also produce error messages for other X Plugin settings
      because it cannot recognize them.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="x-plugin-option-variable-reference"></a>20.5.5.1 X Plugin Option and Variable Reference</h4>
</div>
</div>
</div>
<p>
        This table provides an overview of the command options, and
        system and status variables provided by X Plugin.
</p>
<div class="table">
<a name="idm140091611940448"></a><p class="title"><b>Table 20.1 X Plugin Option and Variable
Reference</b></p>
<div class="table-contents">
<table frame="box" rules="all" summary="Reference for X Plugin command-line options, system variables, and status variables."><col width="20%"><col width="15%"><col width="15%"><col width="15%"><col width="15%"><col width="15%"><col width="15%"><thead><tr><th scope="col">Name</th>
<th scope="col">Cmd-Line</th>
<th scope="col">Option File</th>
<th scope="col">System Var</th>
<th scope="col">Status Var</th>
<th scope="col">Var Scope</th>
<th scope="col">Dynamic</th>
</tr></thead><tbody><tr><td scope="row"><a class="link" href="document-store.html#option_mysqld_mysqlx">mysqlx</a></td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_aborted_clients">Mysqlx_aborted_clients</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_address">Mysqlx_address</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_bind_address">mysqlx_bind_address</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_bytes_received">Mysqlx_bytes_received</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_bytes_sent">Mysqlx_bytes_sent</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_connect_timeout">mysqlx_connect_timeout</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_connection_accept_errors">Mysqlx_connection_accept_errors</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_connection_errors">Mysqlx_connection_errors</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_connections_accepted">Mysqlx_connections_accepted</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_connections_closed">Mysqlx_connections_closed</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_connections_rejected">Mysqlx_connections_rejected</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_crud_create_view">Mysqlx_crud_create_view</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_crud_delete">Mysqlx_crud_delete</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_crud_drop_view">Mysqlx_crud_drop_view</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_crud_find">Mysqlx_crud_find</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_crud_insert">Mysqlx_crud_insert</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_crud_modify_view">Mysqlx_crud_modify_view</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_crud_update">Mysqlx_crud_update</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_document_id_unique_prefix">mysqlx_document_id_unique_prefix</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_enable_hello_notice">mysqlx_enable_hello_notice</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_errors_sent">Mysqlx_errors_sent</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row">Mysqlx_errors_unknown_message_type</td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_expect_close">Mysqlx_expect_close</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_expect_open">Mysqlx_expect_open</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_idle_worker_thread_timeout">mysqlx_idle_worker_thread_timeout</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_init_error">Mysqlx_init_error</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_interactive_timeout">mysqlx_interactive_timeout</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_max_allowed_packet">mysqlx_max_allowed_packet</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_max_connections">mysqlx_max_connections</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_min_worker_threads">mysqlx_min_worker_threads</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_notice_global_sent">Mysqlx_notice_global_sent</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_notice_other_sent">Mysqlx_notice_other_sent</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_notice_warning_sent">Mysqlx_notice_warning_sent</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_notified_by_group_replication">Mysqlx_notified_by_group_replication</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_port">Mysqlx_port</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_port">mysqlx_port</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_port_open_timeout">mysqlx_port_open_timeout</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_read_timeout">mysqlx_read_timeout</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Session</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_rows_sent">Mysqlx_rows_sent</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_sessions">Mysqlx_sessions</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_sessions_accepted">Mysqlx_sessions_accepted</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_sessions_closed">Mysqlx_sessions_closed</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_sessions_fatal_error">Mysqlx_sessions_fatal_error</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_sessions_killed">Mysqlx_sessions_killed</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_sessions_rejected">Mysqlx_sessions_rejected</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_socket">Mysqlx_socket</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_socket">mysqlx_socket</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_accept_renegotiates">Mysqlx_ssl_accept_renegotiates</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_accepts">Mysqlx_ssl_accepts</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_active">Mysqlx_ssl_active</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_ca">mysqlx_ssl_ca</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_capath">mysqlx_ssl_capath</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_cert">mysqlx_ssl_cert</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_cipher">Mysqlx_ssl_cipher</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_cipher">mysqlx_ssl_cipher</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_cipher_list">Mysqlx_ssl_cipher_list</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_crl">mysqlx_ssl_crl</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_crlpath">mysqlx_ssl_crlpath</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_ctx_verify_depth">Mysqlx_ssl_ctx_verify_depth</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_ctx_verify_mode">Mysqlx_ssl_ctx_verify_mode</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_finished_accepts">Mysqlx_ssl_finished_accepts</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_key">mysqlx_ssl_key</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_server_not_after">Mysqlx_ssl_server_not_after</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_server_not_before">Mysqlx_ssl_server_not_before</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_verify_depth">Mysqlx_ssl_verify_depth</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_verify_mode">Mysqlx_ssl_verify_mode</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_ssl_version">Mysqlx_ssl_version</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_create_collection">Mysqlx_stmt_create_collection</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_create_collection_index">Mysqlx_stmt_create_collection_index</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_disable_notices">Mysqlx_stmt_disable_notices</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_drop_collection">Mysqlx_stmt_drop_collection</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_drop_collection_index">Mysqlx_stmt_drop_collection_index</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_enable_notices">Mysqlx_stmt_enable_notices</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_ensure_collection">Mysqlx_stmt_ensure_collection</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_execute_mysqlx">Mysqlx_stmt_execute_mysqlx</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_execute_sql">Mysqlx_stmt_execute_sql</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_execute_xplugin">Mysqlx_stmt_execute_xplugin</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_kill_client">Mysqlx_stmt_kill_client</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_list_clients">Mysqlx_stmt_list_clients</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_list_notices">Mysqlx_stmt_list_notices</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_list_objects">Mysqlx_stmt_list_objects</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_stmt_ping">Mysqlx_stmt_ping</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Both</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_wait_timeout">mysqlx_wait_timeout</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Session</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_worker_threads">Mysqlx_worker_threads</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#statvar_Mysqlx_worker_threads_active">Mysqlx_worker_threads_active</a></td>
<td></td>
<td></td>
<td></td>
<td>Yes</td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="document-store.html#sysvar_mysqlx_write_timeout">mysqlx_write_timeout</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Session</td>
<td>Yes</td>
</tr></tbody></table>
</div>

</div>
<br class="table-break">
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="x-plugin-options-system-variables"></a>20.5.5.2 X Plugin Options and System Variables</h4>

</div>

</div>

</div>
<p>
        To control activation of X Plugin, use this option:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a name="option_mysqld_mysqlx"></a>
            <a class="indexterm" name="idm140091611327584"></a>

            <a class="indexterm" name="idm140091611326544"></a>

            <a class="link" href="document-store.html#option_mysqld_mysqlx"><code class="option">--mysqlx[=value]</code></a>
</p>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx[=value]</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Introduced</strong></span></td>
<td>8.0.11</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Enumeration</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">ON</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Valid Values</strong></span></td>
<td><p class="valid-value"><code class="literal">ON</code></p><p class="valid-value"><code class="literal">OFF</code></p><p class="valid-value"><code class="literal">FORCE</code></p><p class="valid-value"><code class="literal">FORCE_PLUS_PERMANENT</code></p></td>
</tr></tbody></table>
</div>
<p>
            This option controls how the server loads X Plugin at
            startup. In MySQL 8.0, X Plugin is
            enabled by default, but this option may be used to control
            its activation state.
          </p><p>
            The option value should be one of those available for
            plugin-loading options, as described in
            <a class="xref" href="server-administration.html#plugin-loading" title="5.6.1 Installing and Uninstalling Plugins">Section 5.6.1, “Installing and Uninstalling Plugins”</a>.
</p></li></ul>
</div>
<p>
        If X Plugin is enabled, it exposes several system
        variables that permit control over its operation:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a name="sysvar_mysqlx_bind_address"></a>
            <a class="indexterm" name="idm140091611296384"></a>

            <a class="indexterm" name="idm140091611295344"></a>

            <a class="link" href="document-store.html#sysvar_mysqlx_bind_address"><code class="literal">mysqlx_bind_address</code></a>
</p>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_bind_address"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-bind-address=value</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_bind_address">mysqlx_bind_address</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>String</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">*</code></td>
</tr></tbody></table>
</div>
<p>
            The network address on which X Plugin listens for
            TCP/IP connections. This variable is not dynamic and can be
            configured only at startup. This is the X Plugin
            equivalent of the
            <a class="link" href="server-administration.html#sysvar_bind_address"><code class="literal">bind_address</code></a> system
            variable; see that variable description for more
            information.
          </p><p>
            If <a class="link" href="document-store.html#sysvar_mysqlx_bind_address"><code class="literal">mysqlx_bind_address</code></a> is
            specified, its value must be a single non-wildcard IP
            address or host name, or one of the wildcard address formats
            that permit listening on multiple network interfaces
            (<code class="literal">*</code>, <code class="literal">0.0.0.0</code>, or
            <code class="literal">::</code>).
          </p><p>
            An IP address can be specified as an IPv4 or IPv6 address.
            If the value is a host name, X Plugin resolves the
            name to an IP address and binds to that address. If a host
            name resolves to multiple IP addresses, X Plugin uses
            the first IPv4 address if there are any, or the first IPv6
            address otherwise.
          </p><p>
            X Plugin treats different types of addresses as
            follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
                If the address is <code class="literal">*</code>, X Plugin
                accepts TCP/IP connections on all server host IPv4
                interfaces, and, if the server host supports IPv6, on
                all IPv6 interfaces. Use this address to permit both
                IPv4 and IPv6 connections for X Plugin. This value
                is the default.
              </p></li><li class="listitem"><p>
                If the address is <code class="literal">0.0.0.0</code>,
                X Plugin accepts TCP/IP connections on all server
                host IPv4 interfaces.
              </p></li><li class="listitem"><p>
                If the address is <code class="literal">::</code>, X Plugin
                accepts TCP/IP connections on all server host IPv4 and
                IPv6 interfaces.
              </p></li><li class="listitem"><p>
                If the address is an IPv4-mapped address, X Plugin
                accepts TCP/IP connections for that address, in either
                IPv4 or IPv6 format. For example, if X Plugin is
                bound to <code class="literal">::ffff:127.0.0.1</code>, a client
                such as MySQL Shell can connect using
                <code class="option">--host=127.0.0.1</code> or
                <code class="option">--host=::ffff:127.0.0.1</code>.
              </p></li><li class="listitem"><p>
                If the address is a <span class="quote">“<span class="quote">regular</span>”</span> IPv4 or IPv6
                address (such as <code class="literal">127.0.0.1</code> or
                <code class="literal">::1</code>), X Plugin accepts TCP/IP
                connections only for that IPv4 or IPv6 address.
</p></li></ul>
</div>
<p>
            If binding to the address fails, X Plugin produces an
            error and the server does not load it.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_connect_timeout"></a>
            <a class="indexterm" name="idm140091611245184"></a>

            <a class="indexterm" name="idm140091611244144"></a>

            <a class="link" href="document-store.html#sysvar_mysqlx_connect_timeout"><code class="literal">mysqlx_connect_timeout</code></a>
</p>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_connect_timeout"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-connect-timeout=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_connect_timeout">mysqlx_connect_timeout</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">30</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">1</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">1000000000</code></td>
</tr></tbody></table>
</div>
<p>
            The number of seconds X Plugin waits for the first
            packet to be received from newly connected clients. This is
            the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_connect_timeout"><code class="literal">connect_timeout</code></a>; see that
            variable for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_document_id_unique_prefix"></a>
            <a class="indexterm" name="idm140091611204656"></a>

            <a class="indexterm" name="idm140091611203552"></a>

            <a class="link" href="document-store.html#sysvar_mysqlx_document_id_unique_prefix"><code class="literal">mysqlx_document_id_unique_prefix</code></a>
</p>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_document_id_unique_prefix"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-document-id-unique-prefix=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_document_id_unique_prefix">mysqlx_document_id_unique_prefix</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">0</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">0</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">65535</code></td>
</tr></tbody></table>
</div>
<p>
            Sets the first 4 bytes of document IDs generated by the
            server when documents are added to a collection. By setting
            this variable to a unique value per instance, you can ensure
            document IDs are unique across instances. See
            <a class="ulink" href="https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html" target="_top">Understanding Document IDs</a>.

            
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_enable_hello_notice"></a>
            <a class="indexterm" name="idm140091611163712"></a>

            <a class="indexterm" name="idm140091611162608"></a>

            <a class="link" href="document-store.html#sysvar_mysqlx_enable_hello_notice"><code class="literal">mysqlx_enable_hello_notice</code></a>
</p>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_enable_hello_notice"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-document-id-unique-prefix[={OFF|ON}]</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_enable_hello_notice">mysqlx_enable_hello_notice</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Boolean</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">ON</code></td>
</tr></tbody></table>
</div>
<p>
            Controls messages sent to classic MySQL protocol clients
            that try to connect over X Protocol. When enabled,
            clients which do not support X Protocol that attempt
            to connect to the server X Protocol port receive an
            error explaining they are using the wrong protocol.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_idle_worker_thread_timeout"></a>
            <a class="indexterm" name="idm140091611130320"></a>

            <a class="indexterm" name="idm140091611129280"></a>

            <a class="link" href="document-store.html#sysvar_mysqlx_idle_worker_thread_timeout"><code class="literal">mysqlx_idle_worker_thread_timeout</code></a>
</p>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_idle_worker_thread_timeout"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-idle-worker-thread-timeout=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_idle_worker_thread_timeout">mysqlx_idle_worker_thread_timeout</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">60</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">0</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">3600</code></td>
</tr></tbody></table>
</div>
<p>
            The number of seconds after which idle worker threads are
            terminated.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_interactive_timeout"></a>
            <a class="indexterm" name="idm140091611091024"></a>

            <a class="indexterm" name="idm140091611089920"></a>

            <a class="link" href="document-store.html#sysvar_mysqlx_interactive_timeout"><code class="literal">mysqlx_interactive_timeout</code></a>
</p>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_interactive_timeout"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-interactive-timeout=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Introduced</strong></span></td>
<td>8.0.4</td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_interactive_timeout">mysqlx_interactive_timeout</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">28800</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">1</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">2147483</code></td>
</tr></tbody></table>
</div>
<p>
            The default value of the
            <a class="link" href="document-store.html#sysvar_mysqlx_wait_timeout"><code class="literal">mysqlx_wait_timeout</code></a> session
            variable for interactive clients. (The number of seconds to
            wait for interactive clients to timeout.)
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_max_allowed_packet"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_max_allowed_packet"><code class="literal">mysqlx_max_allowed_packet</code></a>
</p><a class="indexterm" name="idm140091611046400"></a><a class="indexterm" name="idm140091611045296"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_max_allowed_packet"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-max-allowed-packet=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_max_allowed_packet">mysqlx_max_allowed_packet</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">67108864</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">512</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">1073741824</code></td>
</tr></tbody></table>
</div>
<p>
            The maximum size of network packets that can be received by
            X Plugin. This is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_max_allowed_packet"><code class="literal">max_allowed_packet</code></a>; see
            that variable for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_max_connections"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_max_connections"><code class="literal">mysqlx_max_connections</code></a>
</p><a class="indexterm" name="idm140091611005808"></a><a class="indexterm" name="idm140091611004768"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_max_connections"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-max-connections=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_max_connections">mysqlx_max_connections</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">100</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">1</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">65535</code></td>
</tr></tbody></table>
</div>
<p>
            The maximum number of concurrent client connections
            X Plugin can accept. This is the X Plugin
            equivalent of
            <a class="link" href="server-administration.html#sysvar_max_connections"><code class="literal">max_connections</code></a>; see that
            variable for more information.
          </p><p>
            For modifications to this variable, if the new value is
            smaller than the current number of connections, the new
            limit is taken into account only for new connections.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_min_worker_threads"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_min_worker_threads"><code class="literal">mysqlx_min_worker_threads</code></a>
</p><a class="indexterm" name="idm140091610964736"></a><a class="indexterm" name="idm140091610963632"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_min_worker_threads"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-min-worker-threads=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_min_worker_threads">mysqlx_min_worker_threads</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">2</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">1</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">100</code></td>
</tr></tbody></table>
</div>
<p>
            The minimum number of worker threads used by X Plugin
            for handling client requests.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_port"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_port"><code class="literal">mysqlx_port</code></a>
</p><a class="indexterm" name="idm140091610925600"></a><a class="indexterm" name="idm140091610924512"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_port"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-port=port_num</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_port">mysqlx_port</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">33060</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">1</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">65535</code></td>
</tr></tbody></table>
</div>
<p>
            The network port on which X Plugin listens for TCP/IP
            connections. This is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_port"><code class="literal">port</code></a>; see that variable for
            more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_port_open_timeout"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_port_open_timeout"><code class="literal">mysqlx_port_open_timeout</code></a>
</p><a class="indexterm" name="idm140091610885184"></a><a class="indexterm" name="idm140091610884080"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_port_open_timeout"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-port-open-timeout=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_port_open_timeout">mysqlx_port_open_timeout</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">0</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">0</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">120</code></td>
</tr></tbody></table>
</div>
<p>
            The number of seconds X Plugin waits for a TCP/IP port
            to become free.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_read_timeout"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_read_timeout"><code class="literal">mysqlx_read_timeout</code></a>
</p><a class="indexterm" name="idm140091610845952"></a><a class="indexterm" name="idm140091610844912"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_read_timeout"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-read-timeout=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Introduced</strong></span></td>
<td>8.0.4</td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_read_timeout">mysqlx_read_timeout</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Session</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">28800</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">30</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">2147483</code></td>
</tr></tbody></table>
</div>
<p>
            The number of seconds that X Plugin waits for blocking
            read operations to complete. After this time, if the read
            operation is not successful, the connection is aborted.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_socket"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_socket"><code class="literal">mysqlx_socket</code></a>
</p><a class="indexterm" name="idm140091610804080"></a><a class="indexterm" name="idm140091610802992"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_socket"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-socket=file_name</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_socket">mysqlx_socket</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>String</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">/tmp/mysqlx.sock</code></td>
</tr></tbody></table>
</div>
<p>
            The path to a Unix socket file which X Plugin uses for
            connections. This setting is only used by MySQL Server when
            running on Unix operating systems. Clients can use this
            socket to connect to MySQL Server using X Plugin.
          </p><p>
            The default <a class="link" href="document-store.html#sysvar_mysqlx_socket"><code class="literal">mysqlx_socket</code></a>
            path and file name is based on the default path and file
            name for the main socket file for MySQL Server, with the
            addition of an <code class="literal">x</code> appended to the file
            name. The default path and file name for the main socket
            file is <code class="literal">/tmp/mysql.sock</code>, therefore the
            default path and file name for the X Plugin socket
            file is <code class="literal">/tmp/mysqlx.sock</code>.
          </p><p>
            If you specify an alternative path and file name for the
            main socket file at server startup using the
            <a class="link" href="server-administration.html#sysvar_socket"><code class="literal">socket</code></a> system variable,
            this does not affect the default for the X Plugin
            socket file. In this situation, if you want to store both
            sockets at a single path, you must set the
            <a class="link" href="document-store.html#sysvar_mysqlx_socket"><code class="literal">mysqlx_socket</code></a> system
            variable as well. For example in a configuration file:
          </p><pre data-lang="ini" class="programlisting">
socket=/home/sockets/mysqld/mysql.sock
mysqlx_socket=/home/sockets/xplugin/xplugin.sock
</pre><p>
            If you change the default path and file name for the main
            socket file at compile time using the
            <a class="link" href="installing.html#option_cmake_mysql_unix_addr"><code class="option">MYSQL_UNIX_ADDR</code></a> compile
            option, this does affect the default for the X Plugin
            socket file, which is formed by appending an
            <code class="literal">x</code> to the
            <a class="link" href="installing.html#option_cmake_mysql_unix_addr"><code class="option">MYSQL_UNIX_ADDR</code></a> file name. If
            you want to set a different default for the X Plugin
            socket file at compile time, use the
            <a class="link" href="installing.html#option_cmake_mysqlx_unix_addr"><code class="option">MYSQLX_UNIX_ADDR</code></a> compile
            option.
          </p><p>
            The <code class="literal">MYSQLX_UNIX_PORT</code> environment variable
            can also be used to set a default for the X Plugin
            socket file at server startup (see
            <a class="xref" href="programs.html#environment-variables" title="4.9 MySQL Program Environment Variables">Section 4.9, “MySQL Program Environment Variables”</a>). If you set this
            environment variable, it overrides the compiled
            <a class="link" href="installing.html#option_cmake_mysqlx_unix_addr"><code class="option">MYSQLX_UNIX_ADDR</code></a> value, but is
            overridden by the
            <a class="link" href="document-store.html#sysvar_mysqlx_socket"><code class="literal">mysqlx_socket</code></a> value.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_ssl_ca"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_ssl_ca"><code class="literal">mysqlx_ssl_ca</code></a>
</p><a class="indexterm" name="idm140091610753792"></a><a class="indexterm" name="idm140091610752704"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_ssl_ca"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-ssl-ca=file_name</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_ca">mysqlx_ssl_ca</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>File name</td>
</tr></tbody></table>
</div>
<p>
            This is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_ssl_ca"><code class="literal">ssl_ca</code></a>; see that variable
            for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_ssl_capath"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_ssl_capath"><code class="literal">mysqlx_ssl_capath</code></a>
</p><a class="indexterm" name="idm140091610722576"></a><a class="indexterm" name="idm140091610721536"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_ssl_capath"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-ssl-capath=dir_name</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_capath">mysqlx_ssl_capath</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Directory name</td>
</tr></tbody></table>
</div>
<p>
            This is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_ssl_capath"><code class="literal">ssl_capath</code></a>; see that
            variable for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_ssl_cert"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_ssl_cert"><code class="literal">mysqlx_ssl_cert</code></a>
</p><a class="indexterm" name="idm140091610691488"></a><a class="indexterm" name="idm140091610690400"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_ssl_cert"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-ssl-cert=name</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_cert">mysqlx_ssl_cert</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>File name</td>
</tr></tbody></table>
</div>
<p>
            This is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_ssl_cert"><code class="literal">ssl_cert</code></a>; see that variable
            for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_ssl_cipher"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_ssl_cipher"><code class="literal">mysqlx_ssl_cipher</code></a>
</p><a class="indexterm" name="idm140091610660288"></a><a class="indexterm" name="idm140091610659248"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_ssl_cipher"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-ssl-cipher=name</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_cipher">mysqlx_ssl_cipher</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>String</td>
</tr></tbody></table>
</div>
<p>
            The SSL cipher to use for X Protocol connections. This
            is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_ssl_cipher"><code class="literal">ssl_cipher</code></a>; see that
            variable for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_ssl_crl"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_ssl_crl"><code class="literal">mysqlx_ssl_crl</code></a>
</p><a class="indexterm" name="idm140091610629120"></a><a class="indexterm" name="idm140091610628032"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_ssl_crl"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-ssl-crl=file_name</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_crl">mysqlx_ssl_crl</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>File name</td>
</tr></tbody></table>
</div>
<p>
            This is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_ssl_crl"><code class="literal">ssl_crl</code></a>; see that variable
            for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_ssl_crlpath"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_ssl_crlpath"><code class="literal">mysqlx_ssl_crlpath</code></a>
</p><a class="indexterm" name="idm140091610597904"></a><a class="indexterm" name="idm140091610596864"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_ssl_crlpath"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-ssl-crlpath=dir_name</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_crlpath">mysqlx_ssl_crlpath</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Directory name</td>
</tr></tbody></table>
</div>
<p>
            This is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_ssl_crlpath"><code class="literal">ssl_crlpath</code></a>; see that
            variable for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_ssl_key"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_ssl_key"><code class="literal">mysqlx_ssl_key</code></a>
</p><a class="indexterm" name="idm140091610566880"></a><a class="indexterm" name="idm140091610565792"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_ssl_key"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-ssl-key=file_name</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_ssl_key">mysqlx_ssl_key</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Global</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>File name</td>
</tr></tbody></table>
</div>
<p>
            This is the X Plugin equivalent of
            <a class="link" href="server-administration.html#sysvar_ssl_key"><code class="literal">ssl_key</code></a>; see that variable
            for more information.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_wait_timeout"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_wait_timeout"><code class="literal">mysqlx_wait_timeout</code></a>
</p><a class="indexterm" name="idm140091610535664"></a><a class="indexterm" name="idm140091610534624"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_wait_timeout"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-wait-timeout=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Introduced</strong></span></td>
<td>8.0.4</td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_wait_timeout">mysqlx_wait_timeout</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Session</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">28800</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">1</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">2147483</code></td>
</tr></tbody></table>
</div>
<p>
            The number of seconds that X Plugin waits for activity
            on a connection. After this time, if the read operation is
            not successful, the connection is aborted. If the client is
            noninteractive, the initial value of the session variable is
            copied from the global
            <a class="link" href="document-store.html#sysvar_mysqlx_wait_timeout"><code class="literal">mysqlx_wait_timeout</code></a>
            variable. For interactive clients, the initial value is
            copied from the session
            <a class="link" href="document-store.html#sysvar_mysqlx_interactive_timeout"><code class="literal">mysqlx_interactive_timeout</code></a>.
          </p></li><li class="listitem"><p><a name="sysvar_mysqlx_write_timeout"></a>
            <a class="link" href="document-store.html#sysvar_mysqlx_write_timeout"><code class="literal">mysqlx_write_timeout</code></a>
</p><a class="indexterm" name="idm140091610490928"></a><a class="indexterm" name="idm140091610489888"></a>
<div class="informaltable">
<table frame="box" rules="all" summary="Properties for mysqlx_write_timeout"><col width="50%"><col width="50%"><thead><tr><th scope="col">Property</th>
<th scope="col">Value</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>Command-Line Format</strong></span></td>
<td><code class="literal">--mysqlx-write-timeout=#</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Introduced</strong></span></td>
<td>8.0.4</td>
</tr><tr><td scope="row"><span class="bold"><strong>System Variable</strong></span></td>
<td><code class="literal"><a class="link" href="document-store.html#sysvar_mysqlx_write_timeout">mysqlx_write_timeout</a></code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Scope</strong></span></td>
<td>Session</td>
</tr><tr><td scope="row"><span class="bold"><strong>Dynamic</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong><a class="link" href="optimization.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax"><code class="literal">SET_VAR</code></a> Hint Applies</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Type</strong></span></td>
<td>Integer</td>
</tr><tr><td scope="row"><span class="bold"><strong>Default Value</strong></span></td>
<td><code class="literal">60</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Minimum Value</strong></span></td>
<td><code class="literal">1</code></td>
</tr><tr><td scope="row"><span class="bold"><strong>Maximum Value</strong></span></td>
<td><code class="literal">2147483</code></td>
</tr></tbody></table>
</div>
<p>
            The number of seconds that X Plugin waits for blocking
            write operations to complete. After this time, if the write
            operation is not successful, the connection is aborted.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="x-plugin-system-monitoring"></a>20.5.6 Monitoring X Plugin</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="document-store.html#x-plugin-monitoring-status-variables">20.5.6.1 Status Variables for X Plugin</a></span></dt></dl>
</div>
<p>
      This section describes how to monitor X Plugin. There are
      two available methods of monitoring, using Performance Schema
      tables or status variables.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="x-plugin-monitoring-status-variables"></a>20.5.6.1 Status Variables for X Plugin</h4>
</div>
</div>
</div>
<p>
        The status variables have the following meanings.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a name="statvar_Mysqlx_aborted_clients"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_aborted_clients"><code class="literal">Mysqlx_aborted_clients</code></a>
          </p><p>
            The number of clients that were disconnected because of an
            input or output error.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_address"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_address"><code class="literal">Mysqlx_address</code></a>
          </p><p>
            The network address which X Plugin is bound to. If the
            bind has failed, or if the
            <a class="link" href="server-administration.html#sysvar_skip_networking"><code class="literal">skip_networking</code></a> option has
            been used, the value shows <code class="literal">UNDEFINED</code>.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_bytes_received"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_bytes_received"><code class="literal">Mysqlx_bytes_received</code></a>
          </p><p>
            The number of bytes received through the network.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_bytes_sent"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_bytes_sent"><code class="literal">Mysqlx_bytes_sent</code></a>
          </p><p>
            The number of bytes sent through the network.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_connection_accept_errors"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_connection_accept_errors"><code class="literal">Mysqlx_connection_accept_errors</code></a>
          </p><p>
            The number of connections which have caused accept errors.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_connection_errors"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_connection_errors"><code class="literal">Mysqlx_connection_errors</code></a>
          </p><p>
            The number of connections which have caused errors.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_connections_accepted"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_connections_accepted"><code class="literal">Mysqlx_connections_accepted</code></a>
          </p><p>
            The number of connections which have been accepted.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_connections_closed"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_connections_closed"><code class="literal">Mysqlx_connections_closed</code></a>
          </p><p>
            The number of connections which have been closed.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_connections_rejected"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_connections_rejected"><code class="literal">Mysqlx_connections_rejected</code></a>
          </p><p>
            The number of connections which have been rejected.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_crud_create_view"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_crud_create_view"><code class="literal">Mysqlx_crud_create_view</code></a>
          </p><p>
            The number of create view requests received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_crud_delete"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_crud_delete"><code class="literal">Mysqlx_crud_delete</code></a>
          </p><p>
            The number of delete requests received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_crud_drop_view"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_crud_drop_view"><code class="literal">Mysqlx_crud_drop_view</code></a>
          </p><p>
            The number of drop view requests received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_crud_find"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_crud_find"><code class="literal">Mysqlx_crud_find</code></a>
          </p><p>
            The number of find requests received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_crud_insert"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_crud_insert"><code class="literal">Mysqlx_crud_insert</code></a>
          </p><p>
            The number of insert requests received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_crud_modify_view"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_crud_modify_view"><code class="literal">Mysqlx_crud_modify_view</code></a>
          </p><p>
            The number of modify view requests received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_crud_update"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_crud_update"><code class="literal">Mysqlx_crud_update</code></a>
          </p><p>
            The number of update requests received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_cursor_close"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_cursor_close"><code class="literal">Mysqlx_cursor_close</code></a>
          </p><p>
            The number of cursor-close messages received
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_cursor_fetch"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_cursor_fetch"><code class="literal">Mysqlx_cursor_fetch</code></a>
          </p><p>
            The number of cursor-fetch messages received
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_cursor_open"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_cursor_open"><code class="literal">Mysqlx_cursor_open</code></a>
          </p><p>
            The number of cursor-open messages received
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_errors_sent"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_errors_sent"><code class="literal">Mysqlx_errors_sent</code></a>
          </p><p>
            The number of errors sent to clients.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_expect_close"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_expect_close"><code class="literal">Mysqlx_expect_close</code></a>
          </p><p>
            The number of expectation blocks closed.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_expect_open"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_expect_open"><code class="literal">Mysqlx_expect_open</code></a>
          </p><p>
            The number of expectation blocks opened.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_init_error"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_init_error"><code class="literal">Mysqlx_init_error</code></a>
          </p><p>
            The number of errors during initialisation.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_notice_global_sent"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_notice_global_sent"><code class="literal">Mysqlx_notice_global_sent</code></a>
          </p><p>
            The number of global notifications sent to clients.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_notice_other_sent"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_notice_other_sent"><code class="literal">Mysqlx_notice_other_sent</code></a>
          </p><p>
            The number of other types of notices sent back to clients.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_notice_warning_sent"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_notice_warning_sent"><code class="literal">Mysqlx_notice_warning_sent</code></a>
          </p><p>
            The number of warning notices sent back to clients.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_notified_by_group_replication"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_notified_by_group_replication"><code class="literal">Mysqlx_notified_by_group_replication</code></a>
          </p><p>
            Number of Group Replication notifications sent to clients
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_port"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_port"><code class="literal">Mysqlx_port</code></a>
          </p><p>
            The TCP port which X Plugin is listening to. If a
            network bind has failed, or if the
            <a class="link" href="server-administration.html#option_mysqld_skip-networking"><code class="option">--skip-networking</code></a> option has
            been used, the value shows <code class="literal">UNDEFINED</code>.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_prep_deallocate"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_prep_deallocate"><code class="literal">Mysqlx_prep_deallocate</code></a>
          </p><p>
            The number of prepared-statement-deallocate messages
            received
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_prep_execute"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_prep_execute"><code class="literal">Mysqlx_prep_execute</code></a>
          </p><p>
            The number of prepared-statement-execute messages received
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_prep_prepare"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_prep_prepare"><code class="literal">Mysqlx_prep_prepare</code></a>
          </p><p>
            The number of prepared-statement messages received
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_rows_sent"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_rows_sent"><code class="literal">Mysqlx_rows_sent</code></a>
          </p><p>
            The number of rows sent back to clients.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_sessions"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_sessions"><code class="literal">Mysqlx_sessions</code></a>
          </p><p>
            The number of sessions that have been opened.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_sessions_accepted"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_sessions_accepted"><code class="literal">Mysqlx_sessions_accepted</code></a>
          </p><p>
            The number of session attempts which have been accepted.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_sessions_closed"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_sessions_closed"><code class="literal">Mysqlx_sessions_closed</code></a>
          </p><p>
            The number of sessions that have been closed.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_sessions_fatal_error"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_sessions_fatal_error"><code class="literal">Mysqlx_sessions_fatal_error</code></a>
          </p><p>
            The number of sessions that have closed with a fatal error.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_sessions_killed"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_sessions_killed"><code class="literal">Mysqlx_sessions_killed</code></a>
          </p><p>
            The number of sessions which have been killed.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_sessions_rejected"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_sessions_rejected"><code class="literal">Mysqlx_sessions_rejected</code></a>
          </p><p>
            The number of session attempts which have been rejected.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_socket"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_socket"><code class="literal">Mysqlx_socket</code></a>
          </p><p>
            The Unix socket which X Plugin is listening to.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_accept_renegotiates"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_accept_renegotiates"><code class="literal">Mysqlx_ssl_accept_renegotiates</code></a>
          </p><p>
            The number of negotiations needed to establish the
            connection.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_accepts"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_accepts"><code class="literal">Mysqlx_ssl_accepts</code></a>
          </p><p>
            The number of accepted SSL connections.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_active"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_active"><code class="literal">Mysqlx_ssl_active</code></a>
          </p><p>
            If SSL is active.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_cipher"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_cipher"><code class="literal">Mysqlx_ssl_cipher</code></a>
          </p><p>
            The current SSL cipher (empty for non-SSL connections).
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_cipher_list"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_cipher_list"><code class="literal">Mysqlx_ssl_cipher_list</code></a>
          </p><p>
            A list of possible SSL ciphers (empty for non-SSL
            connections).
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_ctx_verify_depth"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_ctx_verify_depth"><code class="literal">Mysqlx_ssl_ctx_verify_depth</code></a>
          </p><p>
            

            The certificate verification depth limit currently set in
            ctx.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_ctx_verify_mode"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_ctx_verify_mode"><code class="literal">Mysqlx_ssl_ctx_verify_mode</code></a>
          </p><p>
            The certificate verification mode currently set in ctx.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_finished_accepts"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_finished_accepts"><code class="literal">Mysqlx_ssl_finished_accepts</code></a>
          </p><p>
            The number of successful SSL connections to the server.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_server_not_after"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_server_not_after"><code class="literal">Mysqlx_ssl_server_not_after</code></a>
          </p><p>
            The last date for which the SSL certificate is valid.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_server_not_before"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_server_not_before"><code class="literal">Mysqlx_ssl_server_not_before</code></a>
          </p><p>
            The first date for which the SSL certificate is valid.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_verify_depth"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_verify_depth"><code class="literal">Mysqlx_ssl_verify_depth</code></a>
          </p><p>
            The certificate verification depth for SSL connections.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_verify_mode"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_verify_mode"><code class="literal">Mysqlx_ssl_verify_mode</code></a>
          </p><p>
            The certificate verification mode for SSL connections.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_ssl_version"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_ssl_version"><code class="literal">Mysqlx_ssl_version</code></a>
          </p><p>
            The name of the protocol used for SSL connections.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_create_collection"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_create_collection"><code class="literal">Mysqlx_stmt_create_collection</code></a>
          </p><p>
            The number of create collection statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_create_collection_index"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_create_collection_index"><code class="literal">Mysqlx_stmt_create_collection_index</code></a>
          </p><p>
            The number of create collection index statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_disable_notices"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_disable_notices"><code class="literal">Mysqlx_stmt_disable_notices</code></a>
          </p><p>
            The number of disable notice statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_drop_collection"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_drop_collection"><code class="literal">Mysqlx_stmt_drop_collection</code></a>
          </p><p>
            The number of drop collection statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_drop_collection_index"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_drop_collection_index"><code class="literal">Mysqlx_stmt_drop_collection_index</code></a>
          </p><p>
            The number of drop collection index statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_enable_notices"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_enable_notices"><code class="literal">Mysqlx_stmt_enable_notices</code></a>
          </p><p>
            The number of enable notice statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_ensure_collection"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_ensure_collection"><code class="literal">Mysqlx_stmt_ensure_collection</code></a>
          </p><p>
            The number of ensure collection statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_execute_mysqlx"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_execute_mysqlx"><code class="literal">Mysqlx_stmt_execute_mysqlx</code></a>
          </p><p>
            The number of StmtExecute messages received with namespace
            set to <code class="literal">mysqlx</code>.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_execute_sql"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_execute_sql"><code class="literal">Mysqlx_stmt_execute_sql</code></a>
          </p><p>
            The number of StmtExecute requests received for the SQL
            namespace.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_execute_xplugin"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_execute_xplugin"><code class="literal">Mysqlx_stmt_execute_xplugin</code></a>
          </p><p>
            The number of StmtExecute requests received for the
            X Plugin namespace.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_kill_client"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_kill_client"><code class="literal">Mysqlx_stmt_kill_client</code></a>
          </p><p>
            The number of kill client statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_list_clients"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_list_clients"><code class="literal">Mysqlx_stmt_list_clients</code></a>
          </p><p>
            The number of list client statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_list_notices"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_list_notices"><code class="literal">Mysqlx_stmt_list_notices</code></a>
          </p><p>
            The number of list notice statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_list_objects"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_list_objects"><code class="literal">Mysqlx_stmt_list_objects</code></a>
          </p><p>
            The number of list object statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_stmt_ping"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_stmt_ping"><code class="literal">Mysqlx_stmt_ping</code></a>
          </p><p>
            The number of ping statements received.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_worker_threads"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_worker_threads"><code class="literal">Mysqlx_worker_threads</code></a>
          </p><p>
            The number of worker threads available.
          </p></li><li class="listitem"><p><a name="statvar_Mysqlx_worker_threads_active"></a>
            <a class="link" href="document-store.html#statvar_Mysqlx_worker_threads_active"><code class="literal">Mysqlx_worker_threads_active</code></a>
          </p><p>
            The number of worker threads currently used.
</p></li></ul>
</div>

</div>

</div>

</div>

</div>
<div class="copyright-footer">

</div>
<div class="navfooter">
<hr>
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="mysql-shell-userguide.html">Prev</a></td>
<td width="20%" align="center"><a accesskey="u" href="">Up</a></td>
<td width="40%" align="right"> <a accesskey="n" href="mysql-innodb-cluster-userguide.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Chapter 19 MySQL Shell</td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right" valign="top">Chapter 21 InnoDB Cluster</td>
</tr>
</table>
</div>
</body>
</html>
